dongtuan8547
2016-04-21 05:43
浏览 74
已采纳

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

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

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douji8017 2016-04-21 08:47
    已采纳

    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..

    enter image description here

    已采纳该答案
    打赏 评论
  • doutuobao9736 2016-04-21 06:24

    Tha cause of the difference is the join with table3, since you have 1 and 4 twice in the c_id column and these will duplicate the records.

    I would change the counts as follows:

    COUNT(distinct table1.c_id) AS total_count,
    Count(distinct case when status = 1 then table1.c_id else null end) AS active_count,
    Count(distinct case when status = 2 then table1.c_id else null end) AS cancel_count,
    COUNT(distinct table3.id) AS no_of_occur,
    

    The distinct within the count ensures that only distinct values are counted. However, you will still have an issue with no_of_occur. If that is based on table3.id, then you will still get 3 for p_id 79 in the 1st record because the 1st record in table1 relates to 2 records in table3 and the second record in table1 relates to a 3rd record in table3. So, I believe your expectations are either incorrect or your no_of_occur calculation should not be tied to table3 at all. But in this case you do not even need table3 in the query. This is sg only you can answer.

    打赏 评论
  • dongshuo6185 2016-04-21 06:42

    SUM(status = 1) AS active_count, this always return true(1) for all condition. So you getting wrong output.

    I hope this query will work.Please check.

    SELECT
        DATE_FORMAT(table1.created_at,'%Y-%m-%d'),
        table1.p_id,
        table2.p_name,
        COUNT(table1.c_id) AS total_count,
        SUM(CASE WHEN status= 1 THEN 1  ELSE 0 END) AS active_count,
        SUM(CASE WHEN status= 2 THEN 1  ELSE 0 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
    
    打赏 评论

相关推荐 更多相似问题