CODEWITHSUNDEEP
sqlmultiple-tablesselect-query
Thomas
Thomas

Reputation: 315

SQL select query with two tables

I'm struggling with a task. I need to create a select query which: For each specific listed date shows date and revenue where revenue is number of sold units multiplied with unit price (but ONLY if revenue is greater than or equal to 10 000). There are two tables: product & order. Product contains columns: unittype, price. And order contains columns: unittype, date, number (number of units sold)

This is my try on the select query:

SELECT 
order.date, 
product.price*order.number AS revenue 
FROM product 
INNER JOIN 
order 
ON product.unittype = order.unittype 
WHERE product.price*order.number >= 10000;

None of my results are even close to 10k (between 39 and 1.3k) so I'm wondering if I've typed it wrong or if there are any more efficient ways to type it?

Upvotes: 0

Views: 252

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19194

If this is meant to be for the total for the day (and not the individual line), you need an aggregate and a having clause:

SELECT 
order.date, 
SUM(product.price*order.number) AS revenue 
FROM product 
INNER JOIN 
order 
ON product.unittype = order.unittype 
GROUP BY     order.date
HAVING     SUM(product.price*order.number) >= 10000

Upvotes: 1

Related Questions