adding values and then combining rows

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

adding values and then combining rows

Author

Topic

jneff02
Starting Member

10 Posts

Posted - 2009-07-31 : 16:09:47

I have a query that I've written that returns (among other things) a Part #, and a Quantity. I don't want this to be distinct but rather when it returns multiple Part #'s that are the identical, to add the Quantities together and then post a single Part # with the new, summed, Quantity. Make sense?

Thanks in advance!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-31 : 16:15:43

Table structure and sample ata would help...

but it would be something like this..

Select Part# , SUM(Quantity)
From <UrTable> 
Group By Part#

jneff02
Starting Member

10 Posts

Posted - 2009-07-31 : 16:30:21

Sorry, This is what I have for right now...

SELECT kpart, kmfg, icqty, pmdesc
FROM brparts
WHERE (NOT (iclocmain IN ('NON-STK', 'NEW-STK', ' '))) AND (kmfg = 'GV') AND (icqty > 0) OR
(NOT (iclocmain IN ('NON-STK', 'NEW-STK', ' '))) AND (kmfg = 'MW') AND (icqty > 0) OR
(NOT (iclocmain IN ('NON-STK', 'NEW-STK', ' '))) AND (kmfg = 'NA') AND (icqty > 0)
ORDER BY kpart

So you're saying that to get the results I want I need to place SUM(icqty) in my Select statement?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-31 : 16:40:09

Yes..you need this..

SELECT kpart, kmfg, SUM(icqty), pmdesc
FROM brparts
WHERE (NOT (iclocmain IN ('NON-STK', 'NEW-STK', ' '))) AND (kmfg = 'GV') AND (icqty > 0) OR
(NOT (iclocmain IN ('NON-STK', 'NEW-STK', ' '))) AND (kmfg = 'MW') AND (icqty > 0) OR
(NOT (iclocmain IN ('NON-STK', 'NEW-STK', ' '))) AND (kmfg = 'NA') AND (icqty > 0)
GROUP BY kpart, kmfg, pmdesc
ORDER BY kpart

EDIT: Maybe your query could also Simplified like this??

SELECT kpart, kmfg, SUM(icqty), pmdesc
FROM brparts
WHERE iclocmain NOT IN ('NON-STK', 'NEW-STK', ' ') AND kmfg IN ('GV','MW','NA') AND (icqty > 0) 
GROUP BY kpart, kmfg, pmdesc
ORDER BY kpart

jneff02
Starting Member

10 Posts

Posted - 2009-07-31 : 16:47:29

That worked! You are officially awesome in my book. Thank you very much.

By the way what is the need to GROUP BY kpart, kmfg, pmdesc???

Sorry, I am relatively new to SQL and I'm filled with questions.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-31 : 16:50:38

Whenever you use an aggregate function like SUM etc..you need to have a GROUP BY..

check this...

http://www.w3schools.com/Sql/sql_groupby.asp

btw, see if the modified statement that I posted also works..

jneff02
Starting Member

10 Posts

Posted - 2009-08-03 : 08:54:22

Yes, your modified statement definitely worked. I'm not sure why I didn't write the WHERE like that to begin with but that's why I'm still learning. Thank you very much once again and I'm sure I'll need more help in the near future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources