Oracle SQL - How to divide a count by the sum of a count?

Question

I have a query that currently looks like this (simplified)

 SELECT
       m.academicyr,
       m.ncyeargrp_format AS YEAR,
       Count(*) AS cohort,
       round (Avg (m.per_att),4) AS attendance,
       Round (per_cohort,4)      

   FROM
 (
   SELECT
         s.academy,
         s.academicyr,
         s.adno,
         s.ncyeargrp_format,
         CASE WHEN a.possible_att_marks = 0 THEN NULL
           ELSE (a.present_att_marks / a.possible_att_marks)
             END
               AS per_att,
         a.possible_att_marks,
         a.present_att_marks,
         a.period,
         Count (*) AS cohort,
         Count (*) / Sum(Count(*)) AS per_cohort

   FROM
      VACADEMY_STU_all  s

   LEFT JOIN
        vacademy_attendance a
       ON s.academy = a.academy
      AND s.adno = a.adno
      AND s.term = a.period
      AND s.academicyr = a.academicyr

   WHERE
          s.academy = 'CAN'
          AND s.academicyr = '1617'
          AND a.period = '1'

          GROUP BY
         s.academy,
         s.academicyr,
         s.adno,
         s.ncyeargrp_format,
         a.possible_att_marks,
         a.present_att_marks,
         a.period

  ) m
          GROUP BY
          m.academicyr,
          m.ncyeargrp_format,
          m.per_cohort

          ORDER BY
          To_Number(Trim(regexp_replace(m.ncyeargrp_format, '[A-Za-z]')))

I am trying here to divide the count * by the sum of the count *. I am getting an error 'not a single group function'.

The query works perfectly fine without the 'Round (Per_cohort,4)' in the first select and also removed from the inner select and group by.

The outcome I am expecting is

Year Group Stud Count % of Cohort Ave Att Year 7 126 18.18% 98.10% Year 8 139 20.06% 93.88% Year 9 143 20.63% 90.56% Year 10 143 20.63% 95.94% Year 11 142 20.49% 88.45% Grand Total 693 100.00% 93.28%

I have all of the other columns working perfectly, I just need to percentage cohort, so the value in the cohort column divided by the total of the cohort column, which has been created using count *.

Any help much appreciated.

Thanks

Apologies for the formatting, I couldn't figure out how to change it. — Country girl
– Country girl, Commented Sep 28, 2016 at 10:47
You simply need to group by the same values you want in select list; so, if you need to extract a rounded value, group by the rounded value — Aleksej
– Aleksej, Commented Sep 28, 2016 at 10:51
well change Round (per_cohort,4) to Round (sum(per_cohort),4) in your outer select. — LifeOfPi
– LifeOfPi, Commented Sep 28, 2016 at 10:52

Gordon Linoff · Accepted Answer · 2016-09-28 11:16:09Z

3

If this is the line that is getting the error:

     Count (*) / Sum(Count(*)) AS per_cohort

Then you can fix this by using window functions:

     Count (*) / Sum(Count(*)) over () AS per_cohort

answered Sep 28, 2016 at 11:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Country girl Over a year ago

I tried using this, but unfortunately it doesn't work either.

Tim Biegeleisen · Accepted Answer · 2016-09-28 15:44:11Z

0

WITH cte AS
(
    SELECT s.academy,
           s.academicyr,
           s.adno,
           s.ncyeargrp_format,
           CASE WHEN a.possible_att_marks = 0 THEN NULL
                ELSE (a.present_att_marks / a.possible_att_marks)
           END AS per_att,
           a.possible_att_marks,
           a.present_att_marks,
           a.period,
           COUNT(*) AS cohort
    FROM VACADEMY_STU_all s
    LEFT JOIN vacademy_attendance a
        ON s.academy = a.academy AND
           s.adno = a.adno AND
           s.term = a.period AND
           s.academicyr = a.academicyr
    WHERE s.academy = 'CAN' AND
          s.academicyr = '1617' AND
          a.period = '1'
    GROUP BY s.academy,
             s.academicyr,
             s.adno,
             s.ncyeargrp_format,
             a.possible_att_marks,
             a.present_att_marks,
             a.period
)

SELECT m.academicyr,
       m.ncyeargrp_format AS YEAR,
       m.cohort,
       ROUND(AVG(m.per_att), 4) AS attendance,
       ROUND(m.cohort / (SELECT SUM(cohort) FROM cte), 4)
FROM cte m
GROUP BY m.academicyr,
         m.ncyeargrp_format,
         m.per_cohort
ORDER BY TO_NUMBER(TRIM(REGEXP_REPLACE(m.ncyeargrp_format, '[A-Za-z]')))

edited Sep 28, 2016 at 15:44

answered Sep 28, 2016 at 10:56

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

5 Comments

Country girl Over a year ago

FROM ( WITH cte AS ( SELECT s.academy, s.academicyr, s.adno, s.ncyeargrp_format, CASE WHEN a.possible_att_marks = 0 THEN NULL ELSE (a.present_att_marks / a.possible_att_marks) END AS per_att, a.possible_att_marks, a.present_att_marks, a.period, Count () / Sum(Count()) OVER () AS per_cohort ) SELECT cte.cohort / (SELECT Sum(cohort) FROM cte)

Country girl Over a year ago

I tried putting above what I have done, but the formatting is aweful, sorry. Anyway it's not working as I don't think I have put it in the right place.

Tim Biegeleisen Over a year ago

I have updated my answer with the full query I have in mind.

Country girl Over a year ago

Hi, thanks for the help. This isn't the full query though as it is a nested query. I have this in front of it, which is causing it to fail.

Country girl Over a year ago

SELECT m.academicyr, m.ncyeargrp_format AS YEAR, Count(*) AS cohort, round (Avg (m.per_att),4) AS attendance, m.period, Round (m.per_cohort,4) FROM (

Collectives™ on Stack Overflow

Oracle SQL - How to divide a count by the sum of a count?

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related