SQL Help - 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 2000 Forums

SQL Server Development (2000)

SQL Help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-12 : 11:31:31

I'm trying to get the sum of sales for a product category. Several item numbers can all be in the same product category. For example.
item_no prod_cat
101 N18
102 N18
103 N18 and so on.

The monthlyspecials table only has the item number, so I am trying to return the sales for the entire product category. The dash_product_table contains the item numbers and the product category. This is the SQL I have come up with so far, but it only returns the sales for 1 item number, not all of them in the category.

SELECT monthlyspecials.product_name, monthlyspecials.ggroup, dash_product_sales.Prod_Cat, SUM(dash_product_sales.Sls_Amt) AS total
FROM monthlyspecials LEFT OUTER JOIN
dash_product_sales ON monthlyspecials.item_number = dash_product_sales.Item_No
WHERE
(dash_product_sales.Inv_Dt >= '08 / 01 / 2004')
AND (dash_product_sales.Inv_Dt <= '08 / 31 / 2004')
GROUP BY monthlyspecials.product_name, monthlyspecials.ggroup, dash_product_sales.Prod_Cat

chadmat
The Chadinator

1974 Posts

Posted - 2004-11-12 : 12:18:08

How about this:


SELECT Prod_Cat, SUM(dash_product_sales.Sls_Amt) AS total
FROM monthlyspecials m JOIN dash_product_sales d ON m.item_number = d.Item_No
WHERE d.Inv_Dt BETWEEN '08 / 01 / 2004' AND '08 / 31 / 2004'
GROUP BY d.Prod_Cat

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-12 : 12:23:47

That returns the same result for the sales amount as I previously posted. It returns the sales for the one item number, instead of the entire category.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 13:47:09

can you provide the DDL simplfied as

declare @dash_product_sales table
(
     Sls_Amt       money,
     Prod_Cat      char(3),
     Item_No       int,
     Inv_Dt        datetime
.
.
.
)
declare @monthlyspecials table
(
     product_name  nvarchar(20), 
     ggroup        nvarchar(3), 
     item_number   int
.
.
.
)

and some data to test

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-12 : 14:35:07

I exported 2 text files.
Go to www.rockenbach.net/files.zip

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-15 : 10:40:13

[code]DDL
Declare @dash_product_sales table
(
Prod_Cat varchar(50),
Item_No varchar(10),
Sls_Amt money,
Inv_Dt datetime
)

Declare @monthlyspecials TABLE
(
product_name varchar(50),
item_number varchar(10),
ggroup varchar(50)
)[/code]some data
insert into @monthlySpecials
select 'Avalon Bracket System', 'A18-001', 'Avalon Bracket System' union
select 'Comfort Cover', '98165', 'Comfort Cover' union
select 'Elastrobyte Wafers', '860-229', 'Complete Patient Solutions' union
select 'Falcon Hand Instruments', 'OT-2102', 'Falcon Hand Instruments' union
select 'Interproximal Stripping', '88612', 'Interproximal Stripping' union
select 'Lingual Attachments', '400-003', 'Prewelded Band and Tube Assemblies' union
select 'Marquis Bracket System', 'M18-001', 'Marquis Bracket System' union
select 'Patient Relief Wax', '600-001', 'Complete Patient Solutions' union
select 'Retainer Case with Mirror', '45204', 'Retainer Case with Mirror' union
select 'Retainer Cases', '350-000', 'Complete Patient Solutions' union
select 'TruFit Molar Bands', 'V140-ETCH', 'Prewelded Band and Tube Assemblies' union
select 'TruFlex NiTi Distalizing Springs', '700-100', 'NiTi'

insert into @dash_product_sales
select 'A1 ', 'M18-012',15.0000, '2004-07-15 00:00:00.000' union
select 'A1 ', 'M18-012',22.9500, '2004-07-15 00:00:00.000' union
select 'A1 ', 'M18-001',50.0000, '2004-07-15 00:00:00.000' union
select 'D7 ', '400-003',9.6000, '2004-07-15 00:00:00.000' union
select 'E10', 'V141-ETCH',12.0000, '2004-07-15 00:00:00.000' union
select 'E10', 'V141-ETCH',11.9200, '2004-07-15 00:00:00.000' union
select 'E10', 'V140-ETCH',89.4000, '2004-07-15 00:00:00.000' union
select 'E10', 'V140-ETCH',186.8300, '2004-07-15 00:00:00.000' union
select 'G24', '700-100',34.9900, '2004-07-15 00:00:00.000' union
select 'M37', '88612',-45.9900, '2004-07-15 00:00:00.000' union
select 'M37', '88612',45.9900, '2004-07-15 00:00:00.000' union
select 'O18', 'OT-2102',27.9800, '2004-07-15 00:00:00.000' union
select 'R1 ', '600-001',18.9900, '2004-07-15 00:00:00.000' union
select 'R1 ', '600-001',36.7500, '2004-07-15 00:00:00.000' union
select 'R19', '860-229',10.9900, '2004-07-15 00:00:00.000' union
select 'R3 ', '350-000',14.4900, '2004-07-15 00:00:00.000' union
select 'R34', '98165',.0000, '2004-07-15 00:00:00.000' union
select 'R34', '98165',19.9900, '2004-07-15 00:00:00.000' union
select 'R34', '98167',71.9600, '2004-07-15 00:00:00.000' union
select 'R34', '98167',89.9500, '2004-07-15 00:00:00.000' union
select 'R4M', '45204',15.9900, '2004-07-15 00:00:00.000' union
select 'R4M', '45204',29.9800, '2004-07-15 00:00:00.000'

Expected Results???

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-15 : 11:03:28

I think chadmat solution is correct

quote:

SELECT Prod_Cat, SUM(d.Sls_Amt) AS total
FROM monthlyspecials m JOIN dash_product_sales d ON m.item_number = d.Item_No
WHERE d.Inv_Dt BETWEEN '08 / 01 / 2004' AND '08 / 31 / 2004'
GROUP BY d.Prod_Cat

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-15 : 12:02:38

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources