title: Getting the multiple count() in a sinle select statement without script repitiion
body: <p>Below is a script to get the count in a single select with lot of code redundancy. How really I can clean up the code?</p>
  
  <pre><code>SELECT
  
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='PR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS PR_COUNT_DAY_ONE,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='SR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS SR_COUNT_DAY_ONE,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='EX' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS EX_COUNT_DAY_ONE,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='01' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS RE_COUNT_DAY_ONE,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='33' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS TR_COUNT_DAY_ONE,
  
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='PR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS PR_COUNT_DAY_TWO,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='SR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS SR_COUNT_DAY_TWO,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='EX' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS EX_COUNT_DAY_TWO,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='01' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS RE_COUNT_DAY_TWO,
    (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='33' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE &lt;&gt; LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS TR_COUNT_DAY_TWO
  
  FROM MYSCHEMA.DUAL;
  </code></pre>