CODEWITHSUNDEEP
mysqlsqlselectcountgroup-by
Devin Dixon
Devin Dixon

Reputation: 12403

Aggregating Different Column Types With Group By

I have a schema that looks something like this:

id     type      date
1      like      2013-12-25
2      comment   2013-12-25
3      like      2013-12-26
4      comment   2013-12-26

What I am trying to do is aggregate and count all the likes and comments on a specific and retrieve them in a single query. Right now I am using this:

SELECT 
(CASE WHEN type = 'like' THEN COUNT(id) END)  as likes, 
(CASE WHEN type = 'comment' THEN COUNT(id) END)  as comments,
date_trunc('day', date)
FROM
my_table
GROUP BY date_trunc('day', date), type

The problem is, the rows are not returning in a way that aggregates that data correctly. So how can I retrieve different values on the same column with a group by?

Also, the database being used is Postgres but a Mysql solution with be acceptable answer as well.

Upvotes: 1

Views: 90

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT DAY(tt.DATE) 'day', SUM(tt.type ='like')AS Likes, 
       SUM(tt.type='comment') comments
FROM `test_types` tt
GROUP BY DAY(tt.DATE)

Upvotes: 0

vinayak
vinayak

Reputation: 65

Try this.. 

    SELECT  SUM(IF(type ='like',1,0) )as Likes, 
            SUM(IF(type='comment',1,0)) comments, 
            DAY(DATE) 'day'
    FROM `test_types`
    GROUP BY  DAY(DATE)

Upvotes: 1

Related Questions