• 0

[SQL] Stuck on a query


Question

Hey there, I have been given a long list of "queries" that I need to write out, i've got them all done apart from one which really has me stumped.

I'll show you the question and then show you what I have and the problems i'm having. Hopefully someone can help.

Question:

List the names and addresses of the customers who ordered products worth ?10 or more.>

Tables:Order(OrderNo, OrderDate, CustomerNo,)

OrderedProduct(OrderNo, ProductNo)

Product(ProductNo, ProductDescription, ProductPrice, No_in_stock, Reorder_level)

Customer(CustomerNo, CustomerName, CustomerAddress) >

So basically, i need to get the name and addresses of everyone that's ordered a product (or number of products) that totals over ?10, this means I cant just do a WHERE p.ProductPrice > 10 as p.ProductPrice returns multiple values, and i cannot do SUM as it's not allowed in the syntax.

Here's what i have so far:

SELECT c.CustomerName, SUM( p.ProductPrice ) AS TotalShipping
FROM Customer c
JOIN  `Order` o ON o.CustomerNo = c.CustomerNo
JOIN OrderedProduct op ON op.OrderNo = o.OrderNo
JOIN Product p ON p.ProductNo = op.ProductNo
GROUP BY o.OrderNo

This works find for showing the Customer Name and the Total of all the shipping items, however, i need to get it to only show the ones over ?10, and i cannot do WHERE TotalShipping > 10 - and i'm pretty sure my nested sub-query didn't work properly either.

Is there any ways around this, it's probably quite simple i'm just can't think of it.

Thanks for the help in advance!

p.s. This is one of many questions on my paper, so i'm not asking for help where i could learn it - i've tried many different ways and consider this a last option for educational reasons - i just hope someone can help!

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

This should do it. My understanding of the question was "where a customer ordered any one particular product with a value >=10". If it meant to be understood as "total value of all the products are >=10 then you'd need to use your sum equation...

SELECT CustomerName, CustomerAddress

FROM [Customer] C

JOIN [Order] O ON 0.CustomerNo = 0.CustomerNo

JOIN [OrderedProduct] OP ON OP.OrderNo = O.OrderNo

JOIN [Product] P ON P.ProductNo = OP.ProductNo

where OP.ProductPrice >=10

There's no need to do your select line with table names as the column names are unique to that table.

Edited by Rohdekill
Link to comment
Share on other sites

  • 0

The problem with that is that any order could have 10 products, so OP.ProductPrice only refers to a single product, whereas SUM(op.ProductPrice) will add up all of them.

Link to comment
Share on other sites

  • 0
The problem with that is that any order could have 10 products, so OP.ProductPrice only refers to a single product, whereas SUM(op.ProductPrice) will add up all of them.

That's what I mean. The question is decieving and needs clarification.

List the names and addresses of the customers who ordered products worth ?10 or more.>>

could mean:

"Out of all of the products this person ordered, was there any product whose worth is 10 or more?"

or

"Is thetotal> of all the products the customer ordered worth 10 or more?"

if it's the second, see if replacing my where with your sum() works.

Link to comment
Share on other sites

  • 0

This query will give you orders that have at least one item with a price >= 10

SELECT 
	a.CustomerNo,
	a.OrderNo,
	sum(case when b.price >= 10 then b.price else 0 end) as SumOf10Items,
	sum(case when b.price >= 10 then 1 else 0 end) as NoOfItemsGreaterEq10,
	sum(case when b.price < 10 then 1 else 0 end) as NoOfItemsLess10,
	sum(b.price) as OrderTotal,
FROM
	OrderProduct a
	inner join Product b on b.ProductNo = a.ProductNo
GROUP BY
	a.CustomerNo, a.OrderNo
HAVING
	NoOfItemsGreaterEq10 > 0

Now we can combine this with customers like this:

SELECT 
	*
FROM
	Customer a
	inner join (
		SELECT 
			a.CustomerNo,
			a.OrderNo,
			sum(case when b.price >= 10 then b.price else 0 end) as SumOf10Items,
			sum(case when b.price >= 10 then 1 else 0 end) as NoOfItemsGreaterEq10,
			sum(case when b.price < 10 then 1 else 0 end) as NoOfItemsLess10,
			sum(b.price) as OrderTotal,
		FROM
			OrderProduct a
			inner join Product b on b.ProductNo = a.ProductNo
		GROUP BY
			a.CustomerNo, a.OrderNo
		HAVING
			NoOfItemsGreaterEq10 > 0
) b on b.CustomerNo = a.CustomerNo

If you dont care about Order Level detail, but only the unique list of customers, use this:

SELECT
	distinct a.*
FROM
	Customer a
	inner join (
		SELECT 
			a.CustomerNo,
			a.OrderNo,
			sum(case when b.price >= 10 then b.price else 0 end) as SumOf10Items,
			sum(case when b.price >= 10 then 1 else 0 end) as NoOfItemsGreaterEq10,
			sum(case when b.price < 10 then 1 else 0 end) as NoOfItemsLess10,
			sum(b.price) as OrderTotal,
		FROM
			OrderProduct a
			inner join Product b on b.ProductNo = a.ProductNo
		GROUP BY
			a.CustomerNo, a.OrderNo
		HAVING
			NoOfItemsGreaterEq10 > 0
	) b on b.CustomerNo = a.CustomerNo

Link to comment
Share on other sites

  • 0

Could I not just do:

SELECT c.CustomerName, SUM( p.ProductPrice ) AS TotalShipping
FROM Customer c
JOIN  `Order` o ON o.CustomerNo = c.CustomerNo
JOIN OrderedProduct op ON op.OrderNo = o.OrderNo
JOIN Product p ON p.ProductNo = op.ProductNo
GROUP BY o.OrderNo
HAVING TotalShipping > 10

Link to comment
Share on other sites

  • 0

Well, first, you would have to group by c.CustomerName, not O.OrderNo.

And Im not sure if thats what you want. It will give you a list of customers and the total amount they have ever ordered, as long as that total is > 10.

And so, it will exclude the cheapskate customers that have only ever ordered a total of 10 or less.

Link to comment
Share on other sites

  • 0

Yes, but i am grouping by order, not customer. Any customer may have a number of orderes that may, or may not, total above ten pounds. My original question was to find the customers that have placed an order that total'd above ?10, so that query should work fine!

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.