CODEWITHSUNDEEP
phpmysql
tomp
tomp

Reputation: 75

Group by date and value

I have 4 columns in a table called articles: created_at, articles, keyword and category

I have this query to analyse the data:

SELECT DATE(`created_at`) AS date, COUNT(1) AS articles 
FROM articles 
WHERE `keyword`='keyword1' 
GROUP BY DATE(`created_at`)

This returns total number of articles per day for one keyword.

I would like to have a total and then break this down and categorise the articles as either cat1, cat2 or cat3 for each keyword. So giving a table like this:

|date|keyword|articles|cat1|cat2|cat3|

I can't seem to get this right though, so any help would be appreciated!

Upvotes: 1

Views: 90

Answers (1)

valex
valex

Reputation: 24134

select 
DATE(`created_at`),
keyword,
count(*) as TotalCount,
SUM (case when Category = Cat1 then 1 else 0 end) as Cat1Count,
SUM (case when Category = Cat2 then 1 else 0 end) as Cat2Count,
SUM (case when Category = Cat3 then 1 else 0 end) as Cat3Count

from articles
group by DATE(`created_at`), keyword
order by DATE(`created_at`), keyword

Upvotes: 2

Related Questions