Home » SQL & PL/SQL » SQL & PL/SQL » SUM in WHERE clause
SUM in WHERE clause [message #185786] Thu, 03 August 2006 11:17 Go to next message
jman27
Messages: 37
Registered: September 2005
Member
I have a particular query that sums up data, however, would only like to return results where the SUM is greater than a particular value. For instance... lets say I'm trying to display the total amount of time on the phone for each person on each day (each person can have several phone calls in a day) if they spent more than 50 minutes on the phone for the day (essentially reporting on who is on the phone for more than 50 minutes on a particular day, and how many minutes they actually used):

SELECT pc1.person_name, pc1.date, SUM(pc1.call_length) as total
  FROM phonecalls as pc1
 WHERE (SELECT SUM(pc2.call_length)
          FROM phonecalls as pc2
         WHERE pc1.person_name=pc2.person_name
               and pc1.date=pc2.date
         GROUP BY pc2.person_name) > 50
GROUP BY pc1.person_name,pc1.date
ORDER BY pc1.person_name,pc1.date


Which doesn't really work... but sort of the idea? I think I'm just approaching this the wrong way. If I did something simliar to above, the WHERE clauses will both grow as I add parameters. This seems like I'd be doubling the work the database actually had to perform (hopefully that made sense).

Am I headed in the wrong direction?
Re: SUM in WHERE clause [message #185789 is a reply to message #185786] Thu, 03 August 2006 11:44 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
This is where you use the HAVING clause:

select person_name, date, sum(call_length) as total
  from phonecalls
 group by person_name, date
 having sum(call_length) > 50
 order by person_name, date
Re: SUM in WHERE clause [message #185803 is a reply to message #185789] Thu, 03 August 2006 12:32 Go to previous message
jman27
Messages: 37
Registered: September 2005
Member
Bingo!

Thanks, Todd.
Previous Topic: What is wrong with this query?
Next Topic: TKPROF utility
Goto Forum:
  


Current Time: Tue Dec 06 10:32:32 CST 2016

Total time taken to generate the page: 0.10377 seconds