0

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?

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Thomas
  • 315
  • 1
  • 5
  • 15
  • Your query seems correct to me, which dialect are you using ? Oracle ? – Jean-François Savard Sep 19 '14 at 02:08
  • To troubleshoot, expand your select clause to show the price and number values. – Dan Bracuk Sep 19 '14 at 02:15
  • Not sure why as it should not matter for an inner join, but you can try to have two selects like `select date, revenue from (select order.date as date, product.price*order.number as revenue from product inner join order on product.unittype = order.unittype) where revenue >= 10000;` – eckes Sep 19 '14 at 02:16

1 Answers1

1

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
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Maybe if you put HAVING revenue >= 10000 it would be more efficient no ? – CMPS Sep 19 '14 at 02:13
  • In SQL Server it wouldn't be valid sql. Regardless it don't think it would be more efficient (performance-wise) in any circumstances. – Nick.Mc Sep 19 '14 at 05:48
  • Thank you Nick. It is correct that I wanted to sum together the total of the day and your example gives me a much more promising result! – Thomas Sep 19 '14 at 10:52