diksha msc diksha msc - 9 months ago 32
SQL Question

Use result of multiple rows to do arithmetic operation

I'm writing a query to multiply the count that I receive from subquery to fees amount, But I don't know how to do that. Any help/suggestion?
Oracle query is:

select courseid,coursename,fees*tmp
from course c join registration r on
r.courseid=c.courseid
and tmp IN (select count(*)
from course c join registration r on
r.courseid=c.courseid group by coursename);


I tried to use like a variable tmp ,But i don't think it works in oracle query. Is there an alternative way to do so?

Answer Source

You can't do that, because you can only select data from tables that appeared between FROM and WHERE. The IN operator is a quick way to save having to write a bunch of OR statements, it is not something that can establish a variable in the outer query.

Instead do something like:

select courseid,coursename,fees * COUNT(r.courseID) OVER(PARTITION BY c.coursename)
from course c join registration r on 
r.courseid=c.courseid 

Edit/update: you noted that this query produces too many rows and you only want to see distinct course names. In that case it would be better to just use the registrations table to count the number of people on the course and then multiply the fees:

SELECT
  c.courseid, c.coursename, c.fees * COALESCE(r.numberOfstudents, 0) as courseWorth
FROM
  course c
  LEFT OUTER JOIN
  (select courseid, COUNT(*) as numberofstudents FROM registration GROUP BY courseid) r
  ON c.courseID = r.courseid
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download