Home » SQL & PL/SQL » SQL & PL/SQL » Total sum with Count in same query
Total sum with Count in same query [message #231650] Tue, 17 April 2007 22:07 Go to next message
dreamkitchen
Messages: 3
Registered: April 2007
Junior Member
So I am trying to get a count of people working per store, but also total people working at all stores.

SELECT storeid AS Store#,COUNT(employeeid) AS WorkerCountMay1
FROM emp_shift
WHERE TO_CHAR(time_start,'dd/mm/yyyy')='01/05/2007'
GROUP BY storeid;

Problem is any SUM function or other Count seems to break the Group By. Clues?

thanks
DK
Re: Total sum with Count in same query [message #231667 is a reply to message #231650] Wed, 18 April 2007 00:30 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
I am not too sure of your requirement but if you need the sum per level you can use a sub-query in your parent select clause.

Could you give me a more clear picture of your prob?
Re: Total sum with Count in same query [message #231676 is a reply to message #231650] Wed, 18 April 2007 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many ways:
SQL> break on report
SQL> compute sum of nbemp on report
SQL> select deptno, count(*) nbemp
  2  from emp
  3  group by deptno
  4  /
    DEPTNO      NBEMP
---------- ----------
        30          6
        20          5
        10          3
           ----------
sum                14

3 rows selected.

SQL> select decode(grouping(deptno),1,'Sum',deptno) deptno, count(*) nbemp
  2  from emp
  3  group by rollup(deptno)
  4  /
DEPTNO                                        NBEMP
---------------------------------------- ----------
10                                                3
20                                                5
30                                                6
Sum                                              14

4 rows selected.

SQL> select deptno, count(*) nbemp, 
  2         sum(count(*)) over () Totemp
  3  from emp
  4  group by deptno
  5  /
    DEPTNO      NBEMP     TOTEMP
---------- ---------- ----------
        10          3         14
        20          5         14
        30          6         14

3 rows selected.

Regards
Michel
Re: Total sum with Count in same query [message #232117 is a reply to message #231667] Thu, 19 April 2007 09:04 Go to previous messageGo to next message
dreamkitchen
Messages: 3
Registered: April 2007
Junior Member
Thanks Guys,

I especially like the example of sending SUM(*) over to another newly named column.

I do not really understand what DECODE does.

all the best
DK
Re: Total sum with Count in same query [message #232124 is a reply to message #232117] Thu, 19 April 2007 09:42 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is more complex.
You have to study: rollup operation in group by clause and grouping function.

Regards
Michel
Previous Topic: How to setup INIT.ORA parameters (utl_file_dir)
Next Topic: ALTER TABLE in a trigger
Goto Forum:
  


Current Time: Sat Dec 03 20:26:55 CST 2016

Total time taken to generate the page: 0.07685 seconds