CODEWITHSUNDEEP
sqloracle-database
Javi Torre
Javi Torre

Reputation: 824

Oracle several counts over partition

I have the below query which is outputting the below results:

SELECT
    a,
    b,
    COUNT(1) count,
    round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
FROM
    t1
WHERE
    condition1
GROUP BY
    a,
    b
ORDER BY
    COUNT(1) DESC
A B COUNT PERC
Correct Correct 968272 89.37
Mismatch Correct 52620 4.86
Correct NO VALUES 44630 4.12
Correct Mismatch 10722 0.99
NO VALUES NO VALUES 6217 0.57
Mismatch Mismatch 662 0.06
NO VALUES Mismatch 294 0.03

How could I get the below enhancing the query?

A B COUNT PERC COUNT_TOTAL_A PERC_PARTITION_BY_A
Correct Correct 968272 89.37 1023624 94.59
Mismatch Correct 52620 4.86 53282 98.76
Correct NO VALUES 44630 4.12 1023624 4.36
Correct Mismatch 10722 0.99 1023624 1.05
NO VALUES NO VALUES 6217 0.57 6511 95.48
Mismatch Mismatch 662 0.06 53282 1.24
NO VALUES Mismatch 294 0.03 6511 4.52

Upvotes: 0

Views: 95

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

Try this one:

WITH t AS
    (SELECT a, b, COUNT(1) AS cnt FROM T1 WHERE condition1 GROUP BY a, b)
SELECT DISTINCT
    a,
    b,
    SUM(cnt) OVER (PARTITION BY A, B) COUNT,
    ROUND(RATIO_TO_REPORT(cnt) OVER () * 100, 2) perc,
    SUM(cnt) OVER (PARTITION BY A),
    ROUND(RATIO_TO_REPORT(cnt) OVER(PARTITION BY A) * 100, 2) 
FROM t
ORDER BY 3 DESC

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21075

If you are in doubt how to combine the aggregation with the analytical function on different levels, simple split it in separate CTE

The first CTE calulates the overall count and percentage, the second the same for the A column.

Then join both results to get the complete report.

with cnt as (
select a,b, count(*) count,
  round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
from  tab t1
where 1=1 /* some condition 1 */
group by a,b),
cnt_a as (
select a, count(*) count,
  round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
from  tab t1
where 1=1 /* some condition 1 */
group by a )
select 
  cnt.a, cnt.b, cnt.count, cnt.perc,
  cnt_a.count as count_by_a,
  cnt_a.perc as perc_by_a
from cnt 
join cnt_a on cnt.a = cnt_a.a
order by 3 desc;

A         B              COUNT       PERC COUNT_BY_A  PERC_BY_A
--------- --------- ---------- ---------- ---------- ----------
Correct   Correct       968272      89,37    1023624      94,48
Mismatch  Correct        52620       4,86      53282       4,92
Correct   NO VALUES      44630       4,12    1023624      94,48
Correct   Mismatch       10722 ,99           1023624      94,48
NO VALUES NO VALUES       6217 ,57              6511        ,6        
Mismatch  Mismatch         662 ,06             53282       4,92
NO VALUES Mismatch         294 ,03              6511        ,6

I'm not claimint that there is a simpler solution with one pass, but this solution works.

Interesting is also why you in your sample data expects e.g. for Correct three different values of PERC_PARTITION_BY_A

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

This answers the original version of the question.

If I understand correctly, you want the ratio of the value of b to the overall count. If so, just calculate the ratio directly:

SELECT a, b, COUNT(1) as count,
       SUM(SUM(CASE WHEN b = 'Correct' THEN 1 ELSE ) END)) OVER () * 100.0 / COUNT(*) as perc
FROM t1
WHERE condition1
GROUP BY a, b
ORDER BY COUNT(1) DESC;

Upvotes: 1

Related Questions