title: SQL group by and count fixed column values
body: <p>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:</p>
  <pre><code>SELECT 
    data.type, 
    COUNT(data.type) 
  FROM data 
  GROUP BY data.type;
  </code></pre>
  <p>So, by doing it, I have the result:</p>
  <pre>
   -------------- --------------------- 
  | type         | COUNT(data.type)    |
  |--------------|---------------------|
  | 0            |                   1 |
  | 1            |                  46 |
  | 2            |                  35 |
  | 3            |                 423 |
  | 4            |                  64 |
  | 5            |                  36 |
  | 9            |                   1 |
   -------------- ---------------------
  </pre>
  <p>I know that in the <b>type</b> 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 <b>type</b> values too, with their COUNT value set to 0.</p>
  <p>Based on the above query result, the expected result would be:</p>
  <pre>
   -------------- --------------------- 
  | type         | COUNT(data.type)    |
  |--------------|---------------------|
  | 0            |                   1 |
  | 1            |                  46 |
  | 2            |                  35 |
  | 3            |                 423 |
  | 4            |                  64 |
  | 5            |                  36 |
  | 6            |                   0 |
  | 7            |                   0 |
  | 8            |                   0 |
  | 9            |                   1 |
   -------------- --------------------- 
  </pre>
  <p>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:</p>
  <ol>
  <li><b>TRUNCATE</b> table; (I can't securily import new content if there were old data in the table)</li>
  <li><b>INSERT</b> "control" rows;</li>
  <li><b>LOAD DATA INFILE INTO TABLE</b>;</li>
  <li><b>GROUP/COUNT-1</b> the table content;</li>
  <li><b>DELETE</b> "control" rows; (So I can still work with the table content)</li>
  <li>Do any <b>other jobs</b>;</li>
  </ol>
  <p>But, I was looking for a cleaner way to reach the expected result. If possible, a single query, without a bunch of JOINs.</p>
  <p>I would appreciate any suggestion or advice. Thank you very much!</p>
  <h1>EDIT</h1>
  <p>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.</p>
  <p>So, I have &quot;another&quot; 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?</p>
  <p>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:</p>
  <pre><code>GROUP BY data.type(0,1,2,3,4,5,6,7,8,9)
  </code></pre>
  <p>and it could return these filtered values.</p>
  <p>I am really interested to learn such a command, if it really exists/is possible.</p>
  <p>And again, thank you very much!</p>