I have requirement to count rows with different where clauses from the same table. The following is the required output from me
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 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
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 ); and this is what it returns
GB01 0 0 17 GB01 0 39 0 GB01 80 0 0 NL01 0 0 32 NL01 0 159 0 NL01 341 0 0 Any ideas, how can I achieve what I need?
