CODEWITHSUNDEEP
sqloracle-databasecount
Daniel Lawton
Daniel Lawton

Reputation: 466

SUM(COUNT()) in Oracle

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

Upvotes: 2

Views: 14876

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to select courses along with their total fees, for which you need the number of participants. So select courses and join the number of participants per course:

select
  c.course_id,
  c.fee * a.participants as income,
  c.course_name,
  c.store,
  c.venue,
  c.course_level,
  c.course_date,
  c.duration,
  c.fee,
  c.staff
from course c
join
(
  select course_id, count(*) as participants
  from attendancecheck
  group by course_id
) a on a.course_id = c.course_id
order by income;

Upvotes: 0

Vijay Balebail
Vijay Balebail

Reputation: 185

First calculate the sum based on course table with the group by clause. Then join that based on course Id with attendancecheck .

select
  a.course_id, c."income", c.COURSE_NAME, c.STORE, c.VENUE, c.COURSE_LEVEL,
  c.COURSE_DATE, c.DURATION,c.FEE, c.STAFF
from attendancecheck a,
(
  select
    sum(People_on_course * c.Fee) as "Income",
    c.cource_id, c.COURSE_NAME, c.STORE, c.VENUE, c.COURSE_LEVEL, c.COURSE_DATE,
    c.DURATION, c.FEE, c.STAFF
  from course c
  group by
    c.course_id, c.COURSE_NAME, c.STORE, c.VENUE, c.COURSE_LEVEL, c.COURSE_DATE,
    c.DURATION, c.FEE, c.STAFF
) C
where a.course_id = c.course_id
order by c."Income" ;

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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

Upvotes: 2

Related Questions