problem with sum - 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

General SQL Server Forums

New to SQL Server Programming

problem with sum

Author

Topic

ameya_amu
Starting Member

25 Posts

Posted - 2008-01-14 : 07:12:30

Hello Friends

i have a table stock details with following fields

1)StkID (autogen)
2)Pid( number) -- product id
3)Pdtid( number)---- production table ID
4)SalesId (number)---- sales table ID
5)Quantity(decimal)-- quantity of proction / quantity of sales (i have made production and sales entry in same table i.e if it is production then salesID will be 0 and if it is sales then PDTID will be zero)

I want a (sum of production quantity and sum of sales quantity ) according to PID

i have following data in that table

STKID PID PDTID SalesID Quantity
1 49 77 0 121
2 49 78 0 200
3 1 0 29 212
4 49 0 30 300

in this table
I want a (sum of quantity where PDTID = 0 and sum of quantity where SalesID = 0 ) group by PID

i want following result

STKID PID Production Sales
1 49 321 300
2 1 0 212

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-14 : 07:16:32

[code]Select
STKID, PID,
Sum(case when PDTID = 0 then Quantity else 0 end) as Production,
Sum(case when SalesID = 0 then Quantity else 0 end) as Sales
From table
Group by STKID, PID[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-14 : 07:29:07

The output you requested is possible by dropping out STKID from SELECT and GROUP BY list.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 07:30:59

quote:
Originally posted by harsh_athalye

Select
STKID, PID,
Sum(case when PDTID = 0 then Quantity else 0 end) as Production,
Sum(case when SalesID = 0 then Quantity else 0 end) as Sales
From table
Group by STKID, PID

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

You need to remove STKID from group by i think:-

Select 
 ROW_NUMBER() OVER (ORDER BY PID DESC) AS  STKID,
 PID,
 Sum(case when PDTID = 0 then Quantity else 0 end) as Production,
 Sum(case when SalesID = 0 then Quantity else 0 end) as Sales
From table
Group by  PID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources