Retrieving total count using GROUP BY 2

[kvang]

The statement returns the total count for each different WORKTYPE. But I also want to return the total rows counted in the statement, how can I do this? This column should have the same value for all rows returned. Eventually, I want to find the percentage of count compare to the overall for each WORKTYPE.

SELECT WORKTYPE, COUNT(*) AS ITEMCOUNT
FROM BK_WOBQUEUE 
GROUP BY WORKTYPE

Any help is appreciated.

 

[taupirho]

One way is to use an inline view i.e

SELECT WORKTYPE, COUNT(*) AS ITEMCOUNT,
OVERALL_CNT, 100 *(ITEMCOUNT/OVERALL_CNT) '% Total'
FROM BK_WOBQUEUE ,(select COUNT(*) OVERALL_CNT from BK_WOBQUEUE)
GROUP BY WORKTYPE, OVERALL_CNT

 

[SantaMufasa]

BKOU,

Here is code that should do what you requested:

col percent heading "Percent|of Total" format 99.9
SELECT WORKTYPE
      ,COUNT(*) AS ITEMCOUNT
      ,totcnt
      ,(count(*)/totcnt)*100 percent
  FROM BK_WOBQUEUE
      ,(select count(*) totcnt from BK_WOBQUEUE)
 GROUP BY WORKTYPE, totcnt;

                                Percent
WORKTYPE  ITEMCOUNT     TOTCNT of Total
-------- ---------- ---------- --------
       0       3878       5503     70.5
       5        416       5503      7.6
      11          7       5503       .1
      21        261       5503      4.7
      39          2       5503       .0
      68        516       5503      9.4
      71         92       5503      1.7
      72          6       5503       .1
      73        165       5503      3.0
      83          1       5503       .0
      95         13       5503       .2
      96         45       5503       .8
      97         25       5503       .5
     100         75       5503      1.4
     106          1       5503       .0

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

taupirho, We'll just have to change your name to "QuickDraw McGraw". <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kvang]

It's what I wanted. Thanks!

 

[taupirho]

Dave, I'm tempted to say great minds think alike but that would be bigging me up far too much

 

[SantaMufasa]

I am honoured to be in company of one who thinks so quickly. I doubt that there is any "bigging up" going on.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kvang]

Haha.

 

[DeepDiverMom]

It's what I wanted. Thanks!

If what Mufsa and Taupirho did what you wanted, then it is customary to give them a purple star...click [Thank <them> for this valuable post]