sum in sql - 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 2008 Forums

Transact-SQL (2008)

sum in sql

Author

Topic

papershop
Starting Member

27 Posts

Posted - 2011-08-10 : 21:53:15

Dear All

i want to ask about this :

if i have table sales and it's fill with amount sales and month sales
like this :

product amountsales month
a 100 1
a 150 1
a 200 2
a 250 2
a 150 2
a 75 3
a 175 3
a 150 4
a 200 4
a 105 5

and i would like to have report that sum amount sales by product and month like this

product month1 month2 month3 month4 month5
a 250 600 250 350 105

please help me

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-10 : 21:57:23

[code]
select *
from sales
pivot
(
sum(amountsales)
for month in ([1], [2], [3], [4], [5])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

papershop
Starting Member

27 Posts

Posted - 2011-08-10 : 22:41:45

my data

_month product amountsales
1 ACTHIB 637636
1 TRIOVAX 254544
1 EAX B 317817
1 EAX B 119181
1 PIACEL 677272
1 PIACEL 1354544
1 PIACEL 1523862
1 TIM 155455
1 TACT HIB 243000
1 AHIB 908631
2 NCORT AQ -172200
2 NGIN 31739
2 NGIN -31739
2 FYL 78670
2 FLAGYL -78670
2 AML -220412
2 AML 220412
2 AML M 145014
2 AML M 132171
3 PICEL 3259371
3 OKAX 268125
3 ACIB 1588125
3 VARIP 75341
3 TECT HIB 415800
3 ACIB 1588125
3 VARIP 112656
3 EUX B 114000

and then i use pivot

select familyname, 1,2,3
from (
select _month, familyname, valuenet from dbo.v_fact_sales
WHERE (DATEPART(m, inv_date) >=1) AND (DATEPART(m, inv_date) <=3) and (_Year = YEAR(GETDATE())))up
pivot (sum(valuenet) for _month in ("1","2","3") )as pvt
order by familyname

but the result is

familyname noname noname noname
ACTHIB 1 2 3
ACTONEL 1 2 3
AMARYL 1 2 3
AMARYL M 1 2 3
AMLOGRIX 1 2 3

can anyone fix my syntax so the result will be like this

familyname 1noname noname
ACTHIB 1 2 3
ACTONEL 1 2 3
AMARYL 1 2 3
AMARYL M 1 2 3
AMLOGRIX 1 2 3

@papershop

papershop
Starting Member

27 Posts

Posted - 2011-08-10 : 22:45:07

my data

_month product amountsales
1 ACTHIB 637636
1 TRIOVAX 254544
1 EAX B 317817
1 EAX B 119181
1 PIACEL 677272
1 PIACEL 1354544
1 PIACEL 1523862
1 TIM 155455
1 TACT HIB 243000
1 AHIB 908631
2 NCORT AQ -172200
2 NGIN 31739
2 NGIN -31739
2 FYL 78670
2 FLAGYL -78670
2 AML -220412
2 AML 220412
2 AML M 145014
2 AML M 132171
3 PICEL 3259371
3 OKAX 268125
3 ACIB 1588125
3 VARIP 75341
3 TECT HIB 415800
3 ACIB 1588125
3 VARIP 112656
3 EUX B 114000

and then i use pivot

select familyname, 1,2,3
from (
select _month, familyname, valuenet from dbo.v_fact_sales
WHERE (DATEPART(m, inv_date) >=1) AND (DATEPART(m, inv_date) <=3) and (_Year = YEAR(GETDATE())))up
pivot (sum(valuenet) for _month in ("1","2","3") )as pvt
order by familyname

but the result is

familyname noname noname noname
ACTHIB 1 2 3
ACTONEL 1 2 3
AMARYL 1 2 3
AMARYL M 1 2 3
AMLOGRIX 1 2 3

can anyone fix my syntax so the result will be like this

familyname 1 2 3
ACTHIB 100 200 300
ACTONEL .
AMARYL
AMARYL M
AMLOGRIX

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-10 : 22:52:30

[code]
select familyname, [1], [2], [3]
from (
select _month, familyname, valuenet from dbo.v_fact_sales
WHERE (DATEPART(m, inv_date) >=1) AND (DATEPART(m, inv_date) <=3) and (_Year = YEAR(GETDATE())))up
pivot (sum(valuenet) for _month in ("1","2","3") )as pvt
order by familyname
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources