CODEWITHSUNDEEP
mysql
Sandip Nag
Sandip Nag

Reputation: 998

Sql query for clubbing the data according to date

Table

I have a table like this. Now I want to show the total of same dates of different status in single row. What should be the query?

Expected Result

Created | Total1 | Total2 | Total3

2017-02-28 | 1 | 1 | 2

Upvotes: 1

Views: 83

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

This will give you one row per date created, with columns for each story_status:

SELECT
    `created`,
    SUM(if(`story_status` = 'Draft',`total`,0)) as `Total Draft`,
    SUM(if(`story_status` = 'Private',`total`,0)) as `Total Private`,
    SUM(if(`story_status` = 'Published',`total`,0)) as `Total Published`
FROM table
GROUP BY `created`
ORDER BY `created`

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

you could use a sum for case when for each status and group by

select 
      created
    , sum( case when story_status ='Draft' then total else 0 end ) as Draft_count
    , sum( case when story_status ='Private' then total else 0 end ) as Private_count
    , sum( case when story_status ='Published' then total else 0 end ) as Published_count
from my_table 
group by created

Upvotes: 1

Related Questions