SQL group by and count fixed column values

Question

I'm facing a problem in a data importation script in SQL(MySQL) where I need to GROUP rows by type to COUNT how much rows there are from each type. So far, it isn't really a problem, because I know that I can do:

SELECT 
  data.type, 
  COUNT(data.type) 
FROM data 
GROUP BY data.type;

So, by doing it, I have the result:

 -------------- --------------------- 
| type         | COUNT(data.type)    |
|--------------|---------------------|
| 0            |                   1 |
| 1            |                  46 |
| 2            |                  35 |
| 3            |                 423 |
| 4            |                  64 |
| 5            |                  36 |
| 9            |                   1 |
 -------------- ---------------------

I know that in the type column the values will always be in the range from 0 to 9, like the above result. So, I would like to list not only the existing values in the table content but the missing type values too, with their COUNT value set to 0.

Based on the above query result, the expected result would be:

 -------------- --------------------- 
| type         | COUNT(data.type)    |
|--------------|---------------------|
| 0            |                   1 |
| 1            |                  46 |
| 2            |                  35 |
| 3            |                 423 |
| 4            |                  64 |
| 5            |                  36 |
| 6            |                   0 |
| 7            |                   0 |
| 8            |                   0 |
| 9            |                   1 |
 -------------- --------------------- 

I could trickly INSERT one row of each type before GROUP/COUNT-1 the table content, flagging some other column on INSERT to be able to DELETE these rows after. So, the steps of my importation script would change to:

1. TRUNCATE table; (I can't securily import new content if there were old data in the table)
2. INSERT "control" rows;
3. LOAD DATA INFILE INTO TABLE;
4. GROUP/COUNT-1 the table content;
5. DELETE "control" rows; (So I can still work with the table content)
6. Do any other jobs;

But, I was looking for a cleaner way to reach the expected result. If possible, a single query, without a bunch of JOINs.

I would appreciate any suggestion or advice. Thank you very much!

EDIT

I would like to thank for the answers about CREATE a table to store all types to JOIN it. It really solves the problem. My approach solves it too, but does it storing the types, as you did.

So, I have "another" question, just a clarification, based on the received answers and my desired scope... is it possible to reach the expected result with some MySQL command that will not CREATE a new table and/or INSERT these types?

I don't see any problem, actually, in solve my question storing the types... I just would like to find a simplified command... something like a 'best practice'... some kind of filter... as I could run:

GROUP BY data.type(0,1,2,3,4,5,6,7,8,9)

and it could return these filtered values.

I am really interested to learn such a command, if it really exists/is possible.

And again, thank you very much!

Answer 1

Let's assume that you have a types table with all the valid types:

SELECT t.type, 
       COUNT(data.type) 
FROM data join types t on data.type = t.type
GROUP BY t.type
order by t.type

You should include the explicit order by and not depend on the group by to produce results in a particular order.

Answer 2

The easiest way is to create a table of all type values and then join on that table when getting the count:

select t.type,
  count(d.type)
from types t
left join data d
  on t.type = d.type
group by t.type

See SQL Fiddle with demo

Or you can use the following:

select t.type,
  count(d.type)
from
(
  select 0 type
  union all
  select 1 
  union all
  select 2
  union all
  select 3
  union all
  select 4
  union all
  select 5 
  union all
  select 6
  union all
  select 7
  union all
  select 8
  union all
  select 9 
) t
left join data d
  on t.type = d.type
group by t.type

See SQL Fiddle with Demo

Answer 3

One option would be having a static numbers table with the values 0-9. Not sure if this is the most elegant approach, and if you were using SQL Server, I could think of another approach.

Try something like this:

SELECT 
  numbers.number, 
  COUNT(data.type) 
FROM numbers 
left join data 
  on numbers.number = data.type
GROUP BY numbers.number;

And the SQL Fiddle.

Answer 4

Okay... I think I found it! Thank you all!!! I'm accepting my own answer.

I agree with the @GordonLinoff comment that the best practice refers to store the types values and describe them, so you can keep a concise/understandable database and queries.

But, as far as I've learned, if you have some data which might be an irrelevant information, it is preferable to treat it in some other way than storing it.

So, I developed this query:

SELECT 
  SUM(IF(data.type = 0, 1, 0)) AS `0`, 
  SUM(IF(data.type = 1, 1, 0)) AS `1`, 
  SUM(IF(data.type = 2, 1, 0)) AS `2`, 
  SUM(IF(data.type = 3, 1, 0)) AS `3`, 
  SUM(IF(data.type = 4, 1, 0)) AS `4`, 
  SUM(IF(data.type = 5, 1, 0)) AS `5`, 
  SUM(IF(data.type = 6, 1, 0)) AS `6`, 
  SUM(IF(data.type = 7, 1, 0)) AS `7`, 
  SUM(IF(data.type = 8, 1, 0)) AS `8`, 
  SUM(IF(data.type = 9, 1, 0)) AS `9` 
FROM data;

Not a so faster, optimized and beauty query, but to the size of data I'll manage (less than 100.000 rows each importation) it "manually" does the GROUP/COUNT job, running in 0.13 sec in a common developer machine.

It differs from my expected result just in the way rows and columns are selected - instead of 10 rows with 2 columns I've got 1 row with 10 columns, labeled with the matching type. Also, as we have a standardization to the type value (and we'll not change it for sure) which gives it a name and description, I'm now able to use the type name as the column label, instead of joining to a table with the types info to select a third column in the result (which really, is not that important as it's an importation script based on some standards).

Thank you all so much for the help!