CODEWITHSUNDEEP
sqloracle-databasecountsumdivide
Country girl
Country girl

Reputation: 45

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

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

Upvotes: 0

Views: 5780

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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]')))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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

Upvotes: 3

Related Questions