| SUM in WHERE clause [message #185786] |
Thu, 03 August 2006 11:17  |
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  |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
