CODEWITHSUNDEEP
mysql
Wizard
Wizard

Reputation: 1152

How to return items based on a count

I'm trying to return the most gifted items that belong to an order from the orders table. Currently this returns any item that has been gifted, what I'd like it to do is check that the item has been gifted more than once before returning it, as a way of checking that the gift isn't a one off and it's actually a popular gift choice.

This is what I have currently:

SELECT Item_Type, Item_Desc, item_Cost 
FROM rbickers.Item

LEFT join rbickers.Basket 
ON Item.Item_id = Basket.theItem_id

LEFT join rbickers.order
on basket.ord_ID = Order.order_ID

LEFT join rbickers.Session 
on Order.Session_id = Session.Ses_id 

WHERE Order.order_isGift = "true"
order by item_type

Upvotes: 0

Views: 62

Answers (3)

Bohemian
Bohemian

Reputation: 425043

You need to sum the number of times each item has been gifted, order the results my largest count first, then take only the first row:

SELECT Item_Type, Item_Desc, item_Cost, sum(Basket.qty) as total_ordered
FROM rbickers.Item
LEFT join rbickers.Basket ON Item.Item_id = Basket.theItem_id
LEFT join rbickers.order on basket.ord_ID = Order.order_ID
LEFT join rbickers.Session on Order.Session_id = Session.Ses_id 
WHERE Order.order_isGift = "true"
GROUP BY 1, 2, 3
ORDER BY sum(Basket.qty) DESC
LIMIT 1;

Upvotes: 0

marcelj
marcelj

Reputation: 597

You might be looking for GROUP BY and HAVING:

SELECT Item_Type, Item_Desc, Item_Cost 
FROM Item
JOIN Basket ON theItem_id = Item_ID
JOIN Order ON Order_ID = Ord_ID
WHERE Order_isGift = "true"
GROUP BY Item_ID
HAVING count(1) > 1

Upvotes: 2

Dan Steingart
Dan Steingart

Reputation: 775

Have you thought about having another table which simply counts the gifts as they are gifted? You could then sort on Times_Gifted, and it would save some operations on the backend.

Otherwise, if it is a one off calculation, perhaps something like this

SELECT count(*) FROM `tablename`

with appropriate other filters for your needs (cribbed from http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html)

Good luck!

Upvotes: 1

Related Questions