Home » SQL & PL/SQL » SQL & PL/SQL » Grouping a sum inside a WHERE clause (10g)
Grouping a sum inside a WHERE clause [message #322223] Thu, 22 May 2008 16:45 Go to next message
amardilo
Messages: 37
Registered: February 2007
Member
Hi,

I am not sure if this is possible but I was wondering if someone could help me with my current issue.

I have a select statement that joins 2 tables together on certain dates and indicators matching and where the sum cost of table A is equal to table B (where the dates and indicators have matched). Is anything like this possible

SELECT e.* 
FROM emp e, client c 
WHERE e.client_name = c.name
 AND c.start_date = e.start_date 
 AND SUM(c.cost) = SUM(e.rate) 


Say in the above example I had 3 clients with the same name with a total cost of 100 and I have 10 employees assigned to that client with a rate of 10. I then want a list of those employees (I have other matches for client names and start dates but the costs do not match up and I only want to see a list of employesses who are working on projects that break even).

Does anyone know the answer to this? Is it even possible Is it clear? If not any idea on how I can add more info without getting told off by my boss for giving away info Surprised

[Updated on: Thu, 22 May 2008 23:15] by Moderator

Report message to a moderator

Re: Grouping a sum inside a WHERE clause [message #322224 is a reply to message #322223] Thu, 22 May 2008 16:47 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Grouping a sum inside a WHERE clause [message #322256 is a reply to message #322223] Thu, 22 May 2008 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use subqueries to calculate the sums and join these subqueries.

Regards
Michel
Re: Grouping a sum inside a WHERE clause [message #322338 is a reply to message #322256] Fri, 23 May 2008 03:00 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
Michel Cadot wrote on Thu, 22 May 2008 23:16
Use subqueries to calculate the sums and join these subqueries.

Regards
Michel



Hi there. Thanks for the info. I am not sure how to fit a sum query into this. Would you have an idea how to do this?
Re: Grouping a sum inside a WHERE clause [message #322341 is a reply to message #322338] Fri, 23 May 2008 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not sure how to fit a sum query into this. Would you have an idea how to do this?

How do you get the SUM for each table separately?

Regards
Michel
Re: Grouping a sum inside a WHERE clause [message #322510 is a reply to message #322223] Fri, 23 May 2008 23:18 Go to previous messageGo to next message
chilitilly
Messages: 2
Registered: May 2008
Junior Member
HAVING SUM(c.cost) = SUM(e.rate)
try this it may work
Re: Grouping a sum inside a WHERE clause [message #322517 is a reply to message #322510] Sat, 24 May 2008 00:32 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
chilitilly wrote on Sat, 24 May 2008 06:18
HAVING SUM(c.cost) = SUM(e.rate)
try this it may work

No, it will not.

Regards
Michel

Previous Topic: Oracle query
Next Topic: To Display Preferred Column
Goto Forum:
  


Current Time: Fri Dec 02 14:08:42 CST 2016

Total time taken to generate the page: 0.16127 seconds