Home Count group by enum including possible enum values that have 0 count
Reply: 1

Count group by enum including possible enum values that have 0 count

voutasaurus
1#
voutasaurus Published in 2018-02-14 17:03:39Z

I have a table of items. One of the fields is a category (represented by an enum). Some categories have zero items.

So I did this:

select category, count(*) as total from items group by category;
+------------+-------+
| category   | total |
+------------+-------+
| one        |  6675 |
+------------+-------+

I want to generate a table like this (where two is the other possible enum value):

+------------+-------+
| category   | total |
+------------+-------+
| one        |  6675 |
+------------+-------+
| two        |  0    |
+------------+-------+

How do I do this with an mysql SQL query?

cdaiga
2#
cdaiga Reply to 2018-02-16 04:48:00Z

You can make a fictive dataset of the different categories and do a left join with your original table as shown below.

 SELECT A.category, count(*) total FROM
 (SELECT 'one' as Category 
 UNION ALL
 SELECT 'two' as Category) A
 LEFT JOIN items B
 ON A.Category=B.Category
 GROUP BY B.Category;

If you would prefer to get a list of all the category dynamically, then save them in another table (say All_category_table) then do a join as shown below:

 SELECT A.category, count(*) total FROM
 (SELECT Category FROM All_category_table) A
 LEFT JOIN items B
 ON A.Category=B.Category
 GROUP BY B.Category;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 5.637055 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO