Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle: How to do multiple counts with different where clauses the best way?

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?

like image 696
rojanu Avatar asked Mar 15 '13 11:03

rojanu


People also ask

What is the use of Count in Oracle?

Oracle COUNT() function syntax. The Oracle COUNT() function is an aggregate function that returns the number of items in a group.

What is the difference between group by and count in MySQL?

Firstly, the GROUP BY clause divides the rows in the contacts table into groups based on the values in the last_name column. Secondly, the COUNT () function returns the number of the same last names for each last name. Finally, the HAVING clause returns only groups that have more than one value of the last name.

What is the difference between count () and count (all) in SQL?

1 COUNT (*) function returns the number of items in a group, including NULL and duplicate values. 2 COUNT (DISTINCT expression) function returns the number of unique and non-null items in a group. 3 COUNT (ALL expression) evaluates the expression and returns the number of non-null items in a group, including duplicate values.

What happens when you omit the group by clause in Oracle?

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.


Video Answer


1 Answers

  SELECT
    c_unit_code,
    COUNT(case when YOUR_CONDITIONS_FOR_ADVICE_EXPORT then 1 end) AS ADVICE_EXPORT,
    COUNT(case when YOUR_CONDITIONS_FOR_CONFIRMATION_EXPORT then 1 end) AS CONFIRMATION_EXPORT,
    COUNT(case when YOUR_CONDITIONS_FOR_ISSUANCE_STANDBY then 1 end) AS ISSUANCE_STANDBY
  FROM EXIMTRX.EPLC_MASTER
  GROUP BY c_unit_code
like image 70
Egor Skriptunoff Avatar answered Oct 16 '22 06:10

Egor Skriptunoff