title: SQL group by columns in a table, how to get group with count = 0?
body: <p>I am writing a shell script to generate report of record counts.  </p>
  
  <p>Sample Table &amp; Data : MED_FILE_TEST_RECORD</p>
  
  <pre><code>================================================================================
  R_ID    SOURCE  ELEMENT FILE_STATUS FILE_CREATE_TIME    FILE_NAME
  
  ================================================================================
  1001    Japan   ELE01   Successful      30/05/2014 15:11:23 xxxxxx1.txt
  1002    Japan   ELE01   Corrupt         30/05/2014 15:11:23 xxxxxx2.txt
  1003    Japan   ELE02   Successful      30/05/2014 17:11:23 xxxxxx3.txt
  1004    Japan   ELE02   Successful      30/05/2014 17:11:23 xxxxxx4.txt
  1005    Japan   ELE01   Corrupt         31/05/2014 15:11:23 xxxxxx5.txt
  
  ================================================================================
  </code></pre>
  
  <p>I use the following Oracle SQL to generate report. The results with count are generated properly.</p>
  
  <pre><code>SELECT SOURCE, ELEMENT, FILE_STATUS, FILE_CREATE_TIME as DAY, COALESCE(COUNT(FILE_CREATE_TIME), 0) as FILE_COUNT
  FROM MED_FILE_TEST_RECORD
  WHERE 
  (SOURCE IN ('Japan')) AND 
  (ELEMENT IN ( 'ELE01', 'ELE02' )) AND 
  (FILE_STATUS IN ( 'Corrupt', 'Successful' ))  AND
  (FILE_CREATE_TIME BETWEEN to_date('2014-05-30 00:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2014-06-01 23:59:59','YYYY-MM-DD HH24:MI:SS'))
  GROUP BY SOURCE, ELEMENT, FILE_STATUS, FILE_CREATE_TIME
  ORDER BY DAY, SOURCE, ELEMENT, FILE_STATUS desc;
  </code></pre>
  
  <p>Results with count:</p>
  
  <pre><code>================================================================================ 
  SOURCE  ELEMENT FILE_STATUS FILE_CREATE_TIME    FILE_COUNT
  
  ================================================================================
  Japan   ELE01   Successful  30/05/2014  1
  Japan   ELE01   Corrupt 30/05/2014  1
  Japan   ELE02   Successful  30/05/2014  2
  Japan   ELE01   Corrupt 31/05/2014  1
  ================================================================================
  </code></pre>
  
  <p>It it possible to generate result with count = 0 included like below? So that the report reader can be clear that there is no record at certain time at certain group? Thanks!</p>
  
  <pre><code>================================================================================
  SOURCE  ELEMENT FILE_STATUS FILE_CREATE_TIME    FILE_COUNT
  
  ================================================================================
  Japan   ELE01   Successful  30/05/2014  1
  Japan   ELE01   Corrupt 30/05/2014  1
  Japan   ELE02   Successful  30/05/2014  2
  Japan   ELE02   Corrupt 30/05/2014  0
  Japan   ELE01   Successful  31/05/2014  0
  Japan   ELE01   Corrupt 31/05/2014  1
  Japan   ELE02   Successful  31/05/2014  0
  Japan   ELE02   Corrupt 31/05/2014  0
  Japan   ELE01   Successful  01/06/2014  0
  Japan   ELE01   Corrupt 01/06/2014  0
  Japan   ELE02   Successful  01/06/2014  0
  Japan   ELE02   Corrupt 01/06/2014  0
  </code></pre>