SUM products ordered over a period

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)

SUM products ordered over a period

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-04 : 07:28:41

I have the following data in ProductsOrdered table:

Product Day1 Day2 Day3
a 1 2 0
b 0 0 1
c 3 1 5

And i want the output (sum up all the products ordered for a product):

Product TOTAL
a 3
b 1
c 9

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 07:31:14

SELECT Product, Day1 + Day2 + Day3 AS TOTAL
FROM ProductsOrdered

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

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-04 : 10:40:02

Hi Peso

Thanks for the reply. Is there a way to PIVOT the data? For instance, the table could contain multiple days (I don't know whether it will always be day1, day2, day3; it could be day1, or could be up to day10)?

Thanks

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:52:27

Didnt get that. do you mean your tables structure will change dynamically? how?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 10:53:52

There is no way (besides dynamic sql) to do this.
And you do not want PIVOT. You would want UNPIVOT for this. But still you have to know the column names.

What kind of system are you building if you don't know the number of columns?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 10:55:28

You should REALLY REALLY normalize your table and store the data like this

Table1:
Product, DayNum, DayValue

and data looks like

a 1 1
a 2 2
a 3 0
b 1 0
b 2 0
b 3 1
c 1 3
c 2 1
c 3 5

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 10:57:39

See how easy it is to get your data when normalized your table?

DECLARE	@Table1 TABLE
	(
		Product CHAR(1),
		DayNum TINYINT,
		DayValue TINYINT
	)

INSERT	@Table1
SELECT	'a', 1 ,1 UNION ALL
SELECT	'a', 2, 2 UNION ALL
SELECT	'a', 3, 0 UNION ALL
SELECT	'b', 1, 0 UNION ALL
SELECT	'b', 2, 0 UNION ALL
SELECT	'b', 3, 1 UNION ALL
SELECT	'c', 1, 3 UNION ALL
SELECT	'c', 2, 1 UNION ALL
SELECT	'c', 3, 5

SELECT		Product,
		SUM(DayValue) AS Items
FROM		@Table1
GROUP BY	Product
ORDER BY	Product

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

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-04 : 11:40:10

Hi Guys

Thanks for your replies. Basically, i receive the file as a spreadsheet placed on share and import into a table (using SSIS). The source of this data can be updated (by a different team (and essentially company), hence we would not always receive notification of the change).

I wanted to see if I could code the package to dynamically identify how many rows are populated, and then pump this into a table. I could then SUM the total without needing to know how many days have been received.

I don't know how to do this for the import (and if it can be done dynamically from excel to SQL). Then when I query the table, I could then do some fancy (and neat) code to dynamically SUM on the productRef. DynamicSQL is something we try to avoid, so I don't think I could sneak it in.

Anyway, it was just an idea...... but perhaps that was a little far fetched!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:50:31

You can normalize the imported data from excel with ssis.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources