sum many columns - 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 many columns

Author

Topic

gomo
Starting Member

4 Posts

Posted - 2011-07-21 : 14:32:37

Hi,

I want to sum a number of colums but also show distinct parts, i.e.

currently:
Part no qty net value
123 1 10
456 5 80
123 10 60

so i would like to achieve

Part no qty net value
123 11 70
456 5 80

thanks for your help

mo

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-21 : 15:34:41

Use the SUM function, and group by the Part no.


select PartNo, SUM(Qty), SUM(NetValue) from PartsTable group by PartNo

gomo
Starting Member

4 Posts

Posted - 2011-07-21 : 15:49:59

quote:
Originally posted by sunitabeck

Use the SUM function, and group by the Part no.

select PartNo, SUM(Qty), SUM(NetValue) from PartsTable group by PartNo

This is the query that i have at the moment but it still lists the parts separately.

select spart, sum(soq) as qty,sum(sprice)as net
from salesitems
inner join salesorders
on salesitems.sn = salesorders.son
where salesorders.scustomer = 'abc' and salesitems.sduedate between '2010-04-01 00:00:00.000' and '2011-03-31 00:00:00.000'
group by spart

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-21 : 15:54:31

The query you posted would list one and only one row for each spart. Is that not what you are looking for? Can you post sample data that you are getting and a sample of what you want to get?

gomo
Starting Member

4 Posts

Posted - 2011-07-21 : 15:59:38

quote:
Originally posted by sunitabeck

The query you posted would list one and only one row for each spart. Is that not what you are looking for? Can you post sample data that you are getting and a sample of what you want to get?

What i am trying to get to is how many parts have been sold with net value between dates. So i want one part no with total qty and total net value.

My current query shows the same part many times, as well as the qty and net value.

thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-21 : 17:46:53

quote:
Originally posted by gomo

[quote]
My current query shows the same part many times, as well as the qty and net value.

thanks

Then can you show us the real query you are using? Because the one you listed above is not it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources