Mysql query help

My system consists of two user types - Students and Tutors.

- Tutors can create classes and packs

- Both Students and tutors can purchase classes and packs

Following are the tables involved

Groups

Users- Contains common fields of both user types

Tutor_Details- Tutor specific fields

WebClasses - Classes created by tutors

Learning_Packs - Packs created by tutors Orders - One record per purchase

Order_Details - Multiple records per purchase - as many items in purchase

Payments

The following query produces the list of all users(students and tutors) and displays the 3 fields- User name, orders - number of items purchased and topics - total number of classes and packs created -

SELECT u.name,
       COUNT(DISTINCT( o.id_order ))                                         AS
       orders,
       ( ( COUNT(DISTINCT( wc.id_wc )) ) + ( COUNT(DISTINCT( lp.id_lp )) ) ) AS
       topics
FROM   users AS u
       LEFT JOIN tutor_details AS td
         ON u.id_user = td.id_user
       INNER JOIN groups AS g
         ON u.id_group = g.id_group
       LEFT JOIN webclasses AS wc
         ON td.id_tutor = wc.id_author
       LEFT JOIN learning_packs AS lp
         ON td.id_tutor = lp.id_author
       LEFT JOIN orders AS o
         ON ( u.id_user = o.id_user )
       LEFT JOIN order_details AS od
         ON ( o.id_order = od.id_order )
       LEFT JOIN payments AS p
         ON ( o.id_order = p.id_order )
WHERE  IF(o.id_order != 0, o.order_sta开发者_Go百科tus = 'paid', 1)
       AND IF(p.id_payment != 0, p.payment_status = 'success', 1)
GROUP  BY u.id_user
ORDER  BY u.id_user ASC 

Help needed

Now, I want to add another filter/condition to the topics counting. Only those topics should be counted for which wc.status=1 or lp.status=1. I wish to do the same in a single query. Please note that the condition cannot be added inside the main where block (before the group block) because the query must still display students (who have not taken any class) and tutors who have taken classes with id_status = 0.

The topics count should only consider the classes/packs condition, that's all I want. The query should still display all the users as displayed by the present query.

Thanks,

Sandeepan

Okay, I found the solution myself before anyone else :)

SELECT u.id_user,
       ( Concat(u.name, ' ', u.surname) )                                AS name
       ,
       u.login,
       u.status,
       u.email,
       g.name                                                            AS TYPE
       ,
       u.joined,
       COUNT(DISTINCT( o.id_order ))                                     AS
       orders,
       ( COUNT(DISTINCT( IF(wc.id_status = 1, wc.id_wc, NULL) )) +
           COUNT(DISTINCT( IF(lp.id_status = 1, lp.id_lp, NULL) )) ) AS
       topics
FROM   users AS u
       LEFT JOIN tutor_details AS td
         ON u.id_user = td.id_user
       INNER JOIN groups AS g
         ON u.id_group = g.id_group
       LEFT JOIN webclasses AS wc
         ON td.id_tutor = wc.id_author
       LEFT JOIN learning_packs AS lp
         ON td.id_tutor = lp.id_author
       LEFT JOIN orders AS o
         ON ( u.id_user = o.id_user )
       LEFT JOIN order_details AS od
         ON ( o.id_order = od.id_order )
       LEFT JOIN payments AS p
         ON ( o.id_order = p.id_order )
WHERE  IF(o.id_order != 0, o.order_status = 'paid', 1)
       AND IF(p.id_payment != 0, p.payment_status = 'success', 1)
GROUP  BY u.id_user
ORDER  BY u.id_user ASC