CODEWITHSUNDEEP
sql-servercount
casual-beast
casual-beast

Reputation: 13

SQL Server : count ProductID to get total times sold

I'm having trouble with what seems to be a simple query. I'm trying to get the amount of times an entire product has sold by counting and grouping by the ProductID. I've researched it online and every where I go it's just add a simple COUNT, but when I do it, it still outputs the same numbers of rows.

So if I don't use COUNT (for example) it outputs 1,000 rows, and if I DO use COUNT it outputs 1,000 rows and doesn't give me the correct times sold. They are all listed as "1" and not being grouped and counted. I'm guessing it has something to do with my joins but I can't figure it out.

Here's an example below of what I'm seeing after using the COUNT (I've removed brand and date_added just to make it easier to read). ProductID's are showing more than once even though they should be grouped together and counted.

times_sold | ProductID | title    
---------- | --------- | ---------
1          | 17998     | title 2
1          | 13670     | title 3
1          | 17956     | title 4
1          |  4569     | title 5
1          | 12598     | title 1
1          | 12598     | title 1
1          | 17998     | title 2

And here's the query I'm running:

SELECT TOP (100) PERCENT 
    COUNT(s.ProductID) AS times_sold, 
    s.ProductID, p.title, p.brandname, p.date_added
FROM 
    dbo.TBL_OrderSummary AS s 
INNER JOIN 
    dbo.jewelry AS p ON s.ProductID = p.ProductID
INNER JOIN 
    dbo.sent_items AS i ON s.InvoiceID = i.ID
GROUP BY 
    s.ProductID, p.title, p.brandname, p.flare_type, p.date_added, 
    i.date_order_placed, i.ship_code, p.jewelry
HAVING  
    (p.title LIKE '%stone%') 
    AND (i.date_order_placed > CONVERT(DATETIME, '2016-01-01 00:00:00', 102)) 
    AND (i.ship_code = N'paid') 
    AND (p.flare_type = 'Single flare') 
    AND (p.jewelry LIKE '%plugs%')

Thanks for any help!

Upvotes: 0

Views: 89

Answers (2)

martin
martin

Reputation: 23

Unless you are filtering by your aggregate function (ie. HAVING COUNT(s.ProductID) > 2) then you could move all of your selection criteria to the WHERE line.

So you could try:

select count(s.ProductID) times_sold, s.ProductID, p.title
from dbo.TBL_OrderSummary s inner join dbo.jewelry p on s.ProductID = p.ProductID
    inner join dbo.sent_items i on s.InvoiceID = i.ID
where p.title like '%stone%'
    and i.date_order_placed > CONVERT(DATETIME, '2016-01-01 00:00:00', 102)
    and i.ship_code = N'paid'
    and p.flare_type = 'Single flare' 
    and p.jewelry like '%plugs%'
group by s.ProductID, p.title

Upvotes: 0

Wes Palmer
Wes Palmer

Reputation: 880

The reason why they aren't looking right is because the records aren't the same all the way across in the row. If you have a product name Widget 2 and year made is 2015 and you have another one product name widget and year made 2016 it is only going to count a 1 next to each product because the whole row only appears one time. You will need to limit your group by to get an accurate count.

GROUP BY s.productID, p.title, COUNT(s.productID)

This should give you an accurate count. You are just limiting your group by to a too large of sample to get any unique records. You will have to cut down what is in your select for this to work you need to have s.Product and p.title in your select to match the group by. Hope this helps.

Upvotes: 2

Related Questions