cha cha - 1 month ago 10
MySQL Question

Mysql Crostab query

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.

enter image description here

But I'm getting as follows.

enter image description here

My query is as follows

select
ccx.category_id,
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",
count(c.id)
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


If anyone have this kind of experience will be helpful for me to solve the problem.

Answer

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.

UPDATE

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"

have

COUNT(CASE WHEN (c.type_code = 'READ_IT') THEN 1 ELSE NULL END) as "READ_IT"

Explanation: The 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.

Comments