There are few tables to store contents and categories. Contents and categories are mapped in many to many relationship.
A category can have many contents.
A content can be included in many categories.
Therefore I have created content_cat_xref table to map those tables.
category table - id, name, etc
content table - id, name, type_code(read_it, play_it, prove_it, watch_it)
content_cat_xref - content_id, category_id, etc
I wanted to display categories with content types and count of contents for each content type as follows.
But I'm getting as follows.
My query is as follows
IF(count(c.id) > 0, "True", "False") as Sections_with_content,
(CASE WHEN (c.type_code = 'READ_IT') THEN count(c.id) ELSE 0 END) as "READ_IT",
(CASE WHEN (c.type_code = 'WATCH_IT') THEN count(c.id) ELSE 0 END) as "WATCH_IT",
(CASE WHEN (c.type_code = 'PLAY_IT') THEN count(c.id) ELSE 0 END) as "PLAY_IT",
(CASE WHEN (c.type_code = 'PROVE_IT') THEN count(c.id) ELSE 0 END) as "PROVE_IT",
from content_cat_xref as ccx left outer join content as c
on ccx.content_id = c.id
group by ccx.category_id, c.type_code
Do not group by
c.type_code because it results in the various types to be grouped into separate records. The whole point of the conditional counts is to work on the entire overall group (in this case
category_id) and return only the relevant counts.
You also need to change the way you do the conditional counts. The condition must be within the count() (or sum()) function, not outside of it, since you want the results to show in all cases. So, instead of
(CASE WHEN (c.type_code = 'READ_IT') THEN count(c.id) ELSE 0 END) as "READ_IT"
COUNT(CASE WHEN (c.type_code = 'READ_IT') THEN 1 ELSE NULL END) as "READ_IT"
case statement returns the value
1 if the condition is matched, and return
null if it is not.
Count() function counts all non-null values. Change all the other fields' expression based on the above example.