How to Select the Most Occuring Items in SQL with Names

Question

I'm trying to select the most occurring item. This code works, but I don't know how to get it's respective ItemName from the Item table without it erroring

SELECT 
     TransactionDetail.ItemID, COUNT(*) AS 'AmountSold'
FROM TransactionDetail
GROUP BY TransactionDetail.ItemID
ORDER BY COUNT(*) DESC

Attempted code:

SELECT 
      TransactionDetail.ItemID, 
      COUNT(*) AS 'AmountSold'
FROM TransactionDetail
JOIN Item
ON Item.ItemID = TransactionDetail.ItemID
WHERE Item.ItemID = TransactionDetail.ItemID
GROUP BY TransactionDetail.ItemID
ORDER BY COUNT (*) DESC

Can you share the DDLs for both tables please? – Mureinik Feb 05 '15 at 15:47 — Mureinik, Feb 05 '15 at 15:47

score 3 · Accepted Answer · answered Feb 05 '15 at 15:47

3

SELECT TransactionDetail.ItemID9
      ,Item.ItemName
      , COUNT(*) AS [AmountSold]
FROM TransactionDetail
INNER JOIN Item  ON Item.ItemID = TransactionDetail.ItemID
GROUP BY TransactionDetail.ItemID , Item.ItemName
ORDER BY AmountSold DESC

answered Feb 05 '15 at 15:47

M.Ali

67,945
13
101
127

score 0 · Answer 2 · answered Feb 05 '15 at 16:08

Im not sure how many Items you have in the Item table, but you could group the counts by rolling the query up after the initial select statement you have there.

For example.

Select
Case When rollup.ItemName like 'itemName1' Then count(rollup.ItemID) end as 'itemName1',
Case When rollup.ItemName like 'itemName2' Then count(rollup.ItemID) end as 'itemName2',
Case When rollup.ItemName like 'itemName3' Then count(rollup.ItemID) end as 'itemName3',

from 
(
Select Item.ItemName,
TransactionDetail.ItemID

FROM TransactionDetail
JOIN Item
ON Item.ItemID = TransactionDetail.ItemID
WHERE Item.ItemID = TransactionDetail.ItemID

)as rollup

--you could throw in an order by at the end here to list the results in descending order. If you have thousands of Items, this wont be the best method.

How to Select the Most Occuring Items in SQL with Names

2 Answers2