Total products sold - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Total products sold

Author

Topic

MrPeroni
Starting Member

9 Posts

Posted - 2009-02-11 : 08:41:50

Hi,

I need help too create a query, or help to understand how to create it.

TABLES:

tbl_Orders, tbl_Product, tbl_ProductName

PROBLEM:

The result I want is 1; all the products (name of product) 2; and a total number of each product, how many of each product that have been sold.

The name of the product is located in tbl_ProductName. Each specific product and the status of the product is located in tbl_Product. A order (tbl_Orders) can only contain one specific product.

The result I want should be like this:

ProductName TotalSold

TI17 56
MB-Play 53
Cross2 23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 08:43:08

what have you tried yet?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 08:43:19

See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

MrPeroni
Starting Member

9 Posts

Posted - 2009-02-11 : 09:52:24

I have tried this:

DECLARE @datFrom datetime, @datTo datetime

SET @datFrom ='2009-02-01'
SET @datTo ='2009-02-11'

SELECT DISTINCT(itemn.strName),

(select count(*)
from tbl_Orders o, tbl_Product p
where o.intpreliminaryorderid=p.intorderid) as Total

FROM tbl_preliminaryorder p
inner join tbl_Product p ON o.intpreliminaryorderid = p.intorderid
inner join tbl_ProductName pn ON i.intNameID = pn.intID

WHERE o.dattimestamp > @datFrom
AND o.dattimestamp < @datTo
AND o.blnoutofstock = 1

ORDER BY Total DESC

The result I get is:

ProductName TotalSold
TI17 10500
MB-Play 10500
Cross2 10500

I get the same number in the "TotalSold", a number that can't be valid. Its extremely high and exactly the same for every product.

Thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:56:15

[code]SELECT pn.ProductName,
COUNT(p.intorderid) as Total
FROM tbl_preliminaryorder p
inner join tbl_Product p ON o.intpreliminaryorderid = p.intorderid
inner join tbl_ProductName pn ON i.intNameID = pn.intID

WHERE o.dattimestamp > @datFrom
AND o.dattimestamp < @datTo
AND o.blnoutofstock = 1
GROUP BY pn.ProductName
ORDER BY Total DESC
[/code]

MrPeroni
Starting Member

9 Posts

Posted - 2009-02-11 : 09:56:32

Oh, btw. The products in the result i get seems to be right. Its the products that have been sold in that period of time.

MrPeroni
Starting Member

9 Posts

Posted - 2009-02-11 : 10:07:16

That was fast :)

I get a valid result when I execute the query. Now I know what I was doing wrong :)

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 10:17:43

welcome