加入两个表和总和计数问题

Question

I have three tables

table 1

c_id    c_name         status    p_id         created_at
1       john            1         79        2016-04-13 10:36:44    
2       smith           1         79        2016-04-13 14:25:57
3       phil            1         76        2016-04-18 18:06:21
4       leo             1         76        2016-04-18 15:51:41
5       craig           1         79        2016-04-20 10:44:17
...

table 2

p_id       p_name                         
75         test1             
76         test2             
77         test3             
78         test4             
79         test5             
...

table 3

id        c_id                         
1         1             
2         1             
3         3             
4         4             
5         4             

I need the result:

period        p_id    p_name   total_count  active_count cance_count no_of_occur          
2016-04-13    79      test5    2            2            0           2
2016-04-18    76      test2    2            2            0           2
2016-04-20    79      test5    1            1            0           1
...

For code that I wrote quert but it gives me wrong count

SELECT 
   DATE(DATE_ADD(created_at, INTERVAL 19800 SECOND)) AS period, table1.p_id, 
   MIN(mdcpev.value) AS product_name,
   COUNT(table1.c_id) AS total_count,
   SUM(status = 1) AS active_count,
   SUM(status = 2) AS cancel_count,
   COUNT(table3.id) AS no_of_occur,
     FROM table1
         INNER JOIN table2 AS mdcpev ON mdcpev.p_id = p_id
         LEFT JOIN table3 AS mdspo ON mdspo.c_id = tabe1.c_id
         GROUP BY DATE(DATE_ADD(created_at, INTERVAL 19800 SECOND)),p_id

---

EDIT

my current query result is

   period        p_id    p_name   total_count  active_count cance_count no_of_occur          
    2016-04-13    79      test5    3            3            0           3
    2016-04-18    76      test2    3            3            0           2
    2016-04-20    79      test5    1            1            0           1

Here I got active_count 3 which is wrong it should be 2 and no_of_occurshould be 2 and currently it display 3 Can anyone guide me to the what's wrong in this query ?

Thank you

Answer 1

Try this query, it will work.

    SELECT
        DATE_FORMAT(table1.created_at,'%Y-%m-%d'),
        table1.p_id,
        table2.p_name,
        COUNT(table1.c_id) AS total_count,
        COUNT(CASE WHEN status = 1 THEN table1.c_id ELSE NULL end) AS active_count,
        COUNT(CASE WHEN status = 2 THEN table1.c_id ELSE NULL end) AS cancel_count,
        COUNT(table3.id) AS no_of_occur
           FROM table1
           JOIN table2
           on table2.p_id=table1.p_id
           JOIN table3
           ON table3.c_id=table1.c_id
           Group by DATE_FORMAT(table1.created_at,'%Y-%m-%d'),table1.p_id

Output: I took dumy data as it from question and tried in my system..