Let's say we have two tables, 'Orders' and 'Customers' where the fields are:
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
Orders ------ ItemID(PK) Qty Customers --------- ItemID(FK) CostperUnit
Now if we have to find those Item IDs whose sales figure has gone above $10,000.00, what will be the query for this?
That's a good example of where you would use the HAVING clause with a table join to calculate the quantity times cost per unit.
select Orders.ItemID , sum(Qty * CostperUnit) as Sales from Orders inner join Customers on Orders.ItemID = Customers.ItemID group by Orders.ItemID having sum(Qty * CostperUnit) > 10000
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.