Use of HAVING and WHERE conditon of something with count > 1
Posted on 2011-02-21
I have a tbl named Order_Discount that stores all order line items that get a discount per order.
I need to write a SQL statement that lists the OrderIDs of all orders that have one or more line items discounted.
I've started the SQL but I don't think it's correct:
SELECT OrderID, OrderLineItemID, DiscountYN
WHERE DiscountYN = 'Y'
HAVING COUNT(DiscountYN ) > 1
I need to see all OrderID's along with their OrderLineItemID's where one or more OrderLineItemID has a 'Y' for DiscountYN. I don't want to include only those orders that have ALL discounted order line items, having line item id(s) with no discount is ok too (DiscountYN = 'N') as long as one or more order line items per order have a discount. Can I do something like this: HAVING COUNT(DiscountYN ) > 1 WHERE DiscountYN = 'Y'. I know this is bad syntax - what would be the correct syntax to use in my case?