title: Oracle: How to do multiple counts with different where clauses the best way?
body: <p>I have requirement to count rows with different where clauses from the same table.  The following is the required output from me</p>
  
  <pre><code>Bu   #A   #B  #C  #D #E #F #G  #H  #J  #K  #L   #M  #N
  GB01 267  284 84  45 35 32 458 801 111 899 892  56  99
  NL01 132  844 65  28 26 12 627 321 56  681 1062 127 128
  </code></pre>
  
  <p>Each column has its own criteria, so far I have the following SQL but it already looks ugly and doesn't exactly return what I need</p>
  
  <pre><code>SELECT *  FROM (
    SELECT
      c_unit_code,
      COUNT(*) AS ADVICE_EXPORT,
      0 AS CONFIRMATION_EXPORT,
      0 AS ISSUANCE_STANDBY
    FROM EXIMTRX.EPLC_MASTER
    WHERE (CLS_FLG NOT LIKE 'YES' OR CLS_FLG IS NULL) AND (
      form_of_lc LIKE 'IRREVOCABLE' OR
      form_of_lc LIKE 'REVOCABLE' OR
      form_of_lc LIKE 'IRREVOCABLE TRANSFERABLE' OR
      form_of_lc LIKE 'REVOCABLE TRANSFERABLE') AND our_eng LIKE 'ADVICE'
      GROUP BY c_unit_code
  UNION
    SELECT
      c_unit_code,
      0 AS ADVICE_EXPORT,
      COUNT(*) AS CONFIRMATION_EXPORT,
      0 AS ISSUANCE_STANDBY
    FROM EXIMTRX.EPLC_MASTER
    WHERE (CLS_FLG NOT LIKE 'YES' OR CLS_FLG IS NULL) AND (
      form_of_lc LIKE 'IRREVOCABLE' OR
      form_of_lc LIKE 'REVOCABLE' OR
      form_of_lc LIKE 'IRREVOCABLE TRANSFERABLE' OR
      form_of_lc LIKE 'REVOCABLE TRANSFERABLE') AND our_eng LIKE 'CONFIRMATION'
      GROUP BY c_unit_code
  UNION
    SELECT 
      c_unit_code,
      0 AS ADVICE_EXPORT,
      0 AS CONFIRMATION_EXPORT,
      COUNT(*) AS ISSUANCE_STANDBY
    FROM EXIMTRX.EPLC_MASTER
    WHERE (CLS_FLG NOT LIKE 'YES' OR CLS_FLG IS NULL) AND (
      form_of_lc LIKE 'IRREVOCABLE STANDBY' OR
      form_of_lc LIKE 'REVOCABLE STANDBY' OR
      form_of_lc LIKE 'IRREVOC TRANS STANDBY')
      GROUP BY c_unit_code
  );
  </code></pre>
  
  <p>and this is what it returns</p>
  
  <pre><code>GB01    0   0   17
  GB01    0   39  0
  GB01    80  0   0
  NL01    0   0   32
  NL01    0   159 0
  NL01    341 0   0
  </code></pre>
  
  <p>Any ideas, how can I achieve what I need?</p>