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.
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
Author by
Daniel Lawton
Currently studying Applied Computing at college while being employed as a Software Developer.
Updated on June 04, 2022Comments
-
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 over 5 yearsPlease show table definitions and sample input and output data
-
Bhargav Rao over 5 yearsModerator 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 over 5 yearsThis is perfect. Thank you! I don't know why I had it in my head that I needed a SUM!