SUM(COUNT()) in Oracle

10,509

Why are you summing already counted participants? Just use count, multiply by fee and that's all. Apply left join instead of join in case there are no participants for any course. You could also use subquery to count participants for each course.

demo

select c.course_id, count(a.participant_id) * c.fee as income, c.course_name, 
       c.store, c.venue, c.course_level, c.course_date, c.duration, c.fee, c.staff 
  from course c
  left join attendancecheck a on a.course_id = c.course_id
  group by c.course_id, c.course_name, c.store, c.venue, 
       c.course_level, c.course_date, c.duration, c.fee, c.staff 
  order by income
Share:
10,509
Daniel Lawton
Author by

Daniel Lawton

Currently studying Applied Computing at college while being employed as a Software Developer.

Updated on June 04, 2022

Comments

  • Daniel Lawton
    Daniel Lawton over 1 year

    I'm trying to work out how to make a column based on the count of one column multiplied by the number in another column of a joined table.

    Below is what I've tried so far:

    Attempt 1:

    select a.course_id, sum(count(*)*c.FEE) as "Income", c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF 
    from attendancecheck a
    inner join course c
    on a.course_id = c.course_id
    group by a.course_id, c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF 
    order by "Income"
    

    I get error: ORA-00937: not a single-group group function

    Attempt 2:

    select a.course_id, sum(People_on_course * c.Fee) as "Income", c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF 
    from (SELECT COUNT(a.participant_id) as People_on_course
    from attendancecheck a)
    inner join course c
    on a.course_id = c.course_id
    group by a.course_id, c.COURSE_NAME,c.STORE,c.VENUE,c.COURSE_LEVEL,c.COURSE_DATE,c.DURATION,c.FEE,c.STAFF
    order by "Income"
    

    I get error: ORA-00904: "A"."COURSE_ID": invalid identifier

    If someone can guide me in the correct direction on this, that would be great.

    Cheers, Daniel

    • OldProgrammer
      OldProgrammer over 5 years
      Please show table definitions and sample input and output data
    • Bhargav Rao
      Bhargav Rao over 5 years
      Moderator Note: Please do not vandalize your posts. Once you post a question, they belong to the site and its users. Even if it is no longer useful to you, it might be helpful to someone in the future. The answerers would have also put an effort in writing their answer, which would no longer be useful if you have removed the content from the post. Also, note that by posting on the Stack Exchange network, you've granted a non-revocable right for SE to distribute that content (under the CC BY-SA 3.0 license). By SE policy, any vandalism will be reverted.
  • Daniel Lawton
    Daniel Lawton over 5 years
    This is perfect. Thank you! I don't know why I had it in my head that I needed a SUM!