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

sum

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-18 : 09:48:35

I want to add the totpnd column to have one column appearing as the results. I tried this but it didn't work what am I missing/doing wrong?

SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd, weekdat, dowrdat
FROM Pendingdiary
WHERE (doc = '207')
GROUP BY doc, totovrs, totpnds, totovr, totpnd, weekdat, dowrdat


Doc	Totovrs	Totpnds	Totovr	Totpnd			
207	0	0	5	2
207	0	0	5	1
207	0	0	5	1
207	0	0	5	1
207	0	0	5	2
207	0	0	5	1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-18 : 10:04:35

Remove totpnd from the GROUP BY

SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd, weekdat, dowrdat
FROM Pendingdiary
WHERE (doc = '207')
GROUP BY doc, totovrs, totpnds, totovr, weekdat, dowrdat

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-18 : 10:04:38

Never mind I got it:

SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd
FROM Pendingdiary
WHERE (doc = '207')
GROUP BY doc, totovrs, totpnds, totovr

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-18 : 10:33:51

I'm back again.

How would I get this statement to work:

Update PendingDiary
SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd
FROM Pendingdiary
GROUP BY doc, totovrs, totpnds, totovr

My problem is I've inherited a website but the programmer has retired. She wrote this stored procedure which works but it's not adding the totpnd column. So I created this which works well:

SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd
FROM Pendingdiary
GROUP BY doc, totovrs, totpnds, totovr

Only thing it doesn't update the PendingDiary Table. I wanted to add the above line of code to the bottom of the store procedure.

Here's the complicated stored procedure:

truncate table PendingDiary
insert into PendingDiary
Select  b.doc, isnull(totos,0) as totovrs, isnull(totps,0) as totpnds, isnull(toto,0) as totovr, isnull(totp,0) as totpnd, b.weekdat, b.dowrdat   
From
 (select doc, count(doc) as totos, toto = '0' 
 from #temppend
where clear = 'O' and SDW = 'Y' 
group by  doc 
)a 
right join
 (select doc, count(doc) as totps, totp = '0', weekdat, dowrdat   
 from #temppend
 where SDW = 'Y' 
group by  doc,weekdat, dowrdat    
)b
on b.doc = a.doc

insert into PendingDiary 
Select  d.doc, isnull(totos,0) as totovrs, isnull(totps,0) as totpnds, isnull(toto,0) as totovr, isnull(totp,0) as totpnd, d.weekdat, d.dowrdat   
from
(select doc, totos = '0', count(doc) as toto
from #temppend
where clear = 'O' and SDW = 'N' 
group by doc
)c
right join
(select doc, totps = '0', count(doc) as totp, weekdat, dowrdat 
from #temppend
where SDW = 'N'
group by doc, weekdat, dowrdat 
)d
on d.doc = c.doc


Update PendingDiary
 set dowrdat=offices.dbo.endofmonth(weekdat)  
 where dowrdat is null

drop table #temppend

Or if you know where I can total the column in this procedure I'll add it there.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-18 : 10:37:23

Is this your desired resulkt after the update?

Doc	Totovrs	Totpnds	Totovr	Totpnd			
207	0	0	5	8
207	0	0	5	8
207	0	0	5	8
207	0	0	5	8
207	0	0	5	8
207	0	0	5	8

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-18 : 11:23:44

No here's some data. That big stored procedure produces this which is okay but it's not adding the Totpnd column.


Doc	Totovrs	Totpnds	Totovr	Totpnd	weekdat    dowrdat		
020	0	0	5	2       5/14/2010   5/28/2010 
020	0	0	5	6       5/14/2010   5/28/2010  
207	0	0	5	1       5/14/2010   5/28/2010
207	0	0	5	1       5/14/2010   5/28/2010 
207	0	0	5	4       5/14/2010   5/28/2010 
207	0	0	5	2       5/14/2010   5/28/2010 
007	0	0	5	2       5/14/2010   5/28/2010 
007	0	0	5	4       5/14/2010   5/28/2010  
007	0	0	5	1       5/14/2010   5/28/2010
008	0	0	5	1       5/14/2010   5/28/2010 
008	0	0	5	3       5/14/2010   5/28/2010 
008	0	0	5	6       5/14/2010   5/28/2010

I would like to have this outcome instead where only one doc is shown and the grand total is in the Totpnd column


Doc	Totovrs	Totpnds	Totovr	Totpnd	weekdat    dowrdat
020	0	0	5	8       5/14/2010   5/28/2010 
207	0	0	5	8       5/14/2010   5/28/2010
007	0	0	5	7       5/14/2010   5/28/2010 
008	0	0	5	10      5/14/2010   5/28/2010

I hope that makes sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources