Getting count of count from a Oracle SQL query

Question

I have an oracle SQL query that selects the count of the company from different department.

select 
                 a.cust_nm, 
                 a.cust_acct_nb, 
                 a.cust_company_nb, 
                 to_char(b.case_receive_dt, 'YYYYMM'), 
                 count(*)
from 
                 customer a, case b
where 
                 a.cust_nb = b.case_cust_nb
                 and a.cust_company_nb in 
                     ('01062','01602','01603','01604','01605','01606')
                 and b.case_receive_dt > sysdate -365
                 and b.case_status_cd = 'CC'
group by 
                 a.cust_nm, 
                 a.cust_acct_nb, 
                 a.cust_company_nb, 
                 to_char(b.case_receive_dt, 'YYYYMM')
order by 
                 a.cust_nm, 
                 a.cust_acct_nb, 
                 a.cust_company_nb, 
                 to_char(b.case_receive_dt, 'YYYYMM')

This return the count of a.cust_nm, a.cust_acct_nb, a.cust_company_nb, to_char(b.case_receive_dt, 'YYYYMM')

In the same query, I need one more count of all cust_acct_nb

Eg:-

cust_acct_nb    cust_acct_nb    cust_acct_nb   cust_acct_nb        count(*)    Final_Total
KFC                 1               12             09-10-1991         12
KFC                 1               12             10-10-1991         10     
KFC                 1               12             11-10-1991         10           32
KFC                 2               12             09-10-1991         12
KFC                 2               12             10-10-1991         10     
KFC                 2               12             11-10-1991         15           37

How can I get Final_Total in same query ?

Please help!!

You could create another view/subquery with the total data and join it to your actual query, but there will be a bit overhead. It seems easier to calculate total from the database client side, using the cursor — Raul Andres
– Raul Andres, Commented Jan 10, 2014 at 10:40
I can not use any client script, because it is used in one of the ETS called Jitterbit, it will just return you the result and loop it through. — Sahal
– Sahal, Commented Jan 10, 2014 at 10:43
I think that Running aggregates is what you're looking for: http://docs.oracle.com/cd/E12096_01/books/admintool/admintool_SQL11.html — Raul Andres
– Raul Andres, Commented Jan 10, 2014 at 10:44

Rachcha · Accepted Answer · 2014-01-10 10:45:26Z

2

Use a subquery in the select statement as follows:

  select a.cust_nm,
         a.cust_acct_nb,
         a.cust_company_nb,
         to_char(b.case_receive_dt, 'YYYYMM'),
         count(*),
         (select count(*)
            from customer a1, case b1
           where     a1.cust_nb = b1.case_cust_nb
                 and a1.cust_company_nb in ('01062','01602','01603','01604','01605','01606')
                 and b1.case_receive_dt > sysdate -365
                 and b1.case_status_cd = 'CC'
                 and a1.cust_acct_nb = a.cust_acct_nb)
    from customer a, case b
   where     a.cust_nb = b.case_cust_nb
         and a.cust_company_nb in ('01062','01602','01603','01604','01605','01606')
         and b.case_receive_dt > sysdate -365
         and b.case_status_cd = 'CC'
group by a.cust_nm,
         a.cust_acct_nb,
         a.cust_company_nb,
         to_char(b.case_receive_dt, 'YYYYMM')
order by a.cust_nm,
         a.cust_acct_nb,
         a.cust_company_nb,
         to_char(b.case_receive_dt, 'YYYYMM');

OUTPUT:

cust_acct_nb    cust_acct_nb    cust_acct_nb   cust_acct_nb        count(*)    Final_Total
KFC                 1               12             09-10-1991         12           32
KFC                 1               12             10-10-1991         10            32
KFC                 1               12             11-10-1991         10           32
KFC                 2               12             09-10-1991         12           37
KFC                 2               12             10-10-1991         10           37
KFC                 2               12             11-10-1991         15           37

answered Jan 10, 2014 at 10:45

Rachcha

8,8969 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sahal Over a year ago

Thanks, it works line a charm. One more question I have is, I do not want to order by name (cust_acct_nb). But If I remove it from group by clause, it send error because it is there in the select. What should I do for this ?

Rachcha Over a year ago

If you are using an aggregate function (e.g., count, sum, etc.) in your query then all the columns you are referring to in your select and order by clauses will also have to be included in the group by clause. Read more on Oracle Docs by googling for group by.

Srini V · Accepted Answer · 2014-01-10 10:53:33Z

1

Can you try this? I cant test it since I have no data setup.

select 
                 a.cust_nm, 
                 a.cust_acct_nb, 
                 a.cust_company_nb, 
                 to_char(b.case_receive_dt, 'YYYYMM'), 
                 count(*)
from 
                 customer a, case b
where 
                 a.cust_nb = b.case_cust_nb
                 and a.cust_company_nb in 
                     ('01062','01602','01603','01604','01605','01606')
                 and b.case_receive_dt > sysdate -365
                 and b.case_status_cd = 'CC'
group by rollup
                 (a.cust_nm, 
                 a.cust_acct_nb, 
                 a.cust_company_nb, 
                 to_char(b.case_receive_dt, 'YYYYMM'))
order by 
                 a.cust_nm, 
                 a.cust_acct_nb, 
                 a.cust_company_nb, 
                 to_char(b.case_receive_dt, 'YYYYMM')

answered Jan 10, 2014 at 10:53

Srini V

11.5k15 gold badges70 silver badges93 bronze badges

2 Comments

Sahal Over a year ago

Where is the first count here ? Or count of count in select ?

Srini V Over a year ago

second count is in roll up

Sai · Accepted Answer · 2014-01-10 13:47:01Z

0

- Try This:

select 
             a.cust_nm,  
             a.cust_company_nb, 
             to_char(b.case_receive_dt, 'dd-mm-yyyy'),
             decode(grouping(a.cust_acct_nb),1,'Sum of group',a.cust_acct_nb), 
             count(*)
from 
             customer a, case b
where 
             a.cust_nb = b.case_cust_nb
             and a.cust_company_nb in 
                 ('01062','01602','01603','01604','01605','01606')
             and b.case_receive_dt > sysdate -365
             and b.case_status_cd = 'CC'
group by rollup(
             a.cust_nm, 
             a.cust_acct_nb, 
             a.cust_company_nb, 
             to_char(b.case_receive_dt, 'dd-mm-yyyy'));

answered Jan 10, 2014 at 13:47

Sai

6994 gold badges13 silver badges21 bronze badges

Comments

Nazarii Bardiuk · Accepted Answer · 2014-01-10 10:53:20Z

Oracle SQL gives you possibility to query totals and subtotals in one query with ROLLUP extension. Here is an example of such functionality:

SELECT fact_1_id,
   fact_2_id,
   SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE
---------- ---------- -----------
     1          1     4363.55
     1          2     4794.76
     1          3     4718.25
     1          4     5387.45
     1          5     5027.34
     1               24291.35
     2          1     5652.84
     2          2     4583.02
     2          3     5555.77
     2          4     5936.67
     2          5     4508.74
     2               26237.04
                     50528.39

You can get more examples here http://www.oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php#rollup

Collectives™ on Stack Overflow

Getting count of count from a Oracle SQL query

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related