adding 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

General SQL Server Forums

New to SQL Server Programming

adding columns

Author

Topic

lingtrin
Starting Member

13 Posts

Posted - 2007-07-12 : 12:40:40

Hi, hope someone can help me with this, I have something like this:

sum(per1) as period1, sum(per2) as period2, sum(per3) as period3

And I want to add all the periodX toghether, how can I do it?

On my real SQL query the SUM statement is more complex, with some cases and such.

Also can I divide this result by a number?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-12 : 13:04:05

sum(per1) as period1, sum(per2) as period2, sum(per3) as period3, sum(per1+per2+per3)

or

select period1, period2, period3, sumperiod = period1 + period2 + period3
from
(
select sum(per1) as period1, sum(per2) as period2, sum(per3) as period3
....
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lingtrin
Starting Member

13 Posts

Posted - 2007-07-12 : 17:12:32

I have had tried:
sum(per1) as period1, sum(per2) as period2, sum(per3) as period3, sum(per1+per2+per3)
But I get an error in query analyzer, also on your other sample I get an error.

I'm on SQL Server 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 18:27:29

What is the error message ?
Can you post the query that you used ?

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

lingtrin
Starting Member

13 Posts

Posted - 2007-07-13 : 10:26:31

This is the query I use, and where I get the periodX values wich I want to add them toghether.
****************************
SELECT TOP 100 PERCENT ITEMNMBR, trxloctn, SUM(CASE WHEN datediff(month, docdate, getdate()) = 0 THEN TRXQTY ELSE 0 END) AS period1,
SUM(CASE WHEN datediff(month, docdate, getdate()) = 1 THEN TRXQTY ELSE 0 END) AS period2, SUM(CASE WHEN datediff(month, docdate, getdate())
= 2 THEN TRXQTY ELSE 0 END) AS period3, SUM(CASE WHEN datediff(month, docdate, getdate()) = 3 THEN TRXQTY ELSE 0 END) AS period4,
SUM(CASE WHEN datediff(month, docdate, getdate()) = 4 THEN TRXQTY ELSE 0 END) AS period5, SUM(CASE WHEN datediff(month, docdate, getdate())
= 5 THEN TRXQTY ELSE 0 END) AS period6
FROM dbo.IV30300
WHERE trxloctn = 'mp' AND TRXQTY < 0
GROUP BY ITEMNMBR, trxloctn
************************
If I use sum(period1, period2,...) I get the error: "Function SUM requires 1 argument"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-13 : 11:16:47

to sum all 6 periods:

SUM(CASE WHEN datediff(month,docdate,getdate()) in (0,1,2,3,4,5) THEN TRXQTY ELSE 0 END)

CODO ERGO SUM

lingtrin
Starting Member

13 Posts

Posted - 2007-07-13 : 13:18:23

Great thanx, will try that

lingtrin
Starting Member

13 Posts

Posted - 2007-07-13 : 14:16:59

It did work, thanks a lot, now I have another question, what if I want to divide (/) this sum by a number? something like:
SUM(CASE WHEN datediff(month,docdate,getdate()) in (0,1,2,3,4,5) THEN TRXQTY ELSE 0 END) as total, total2 = total / 6?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 15:02:37

In general, SQL Server is much faster summing up "SUM(a) + SUM(b)" than "SUM(a + b)".

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-13 : 15:42:25

quote:
Originally posted by Peso

In general, SQL Server is much faster summing up "SUM(a) + SUM(b)" than "SUM(a + b)".

Peter Larsson
Helsingborg, Sweden

In this case, it is summing a case statement, so I really didn’t feel like writing that code.

By the way, have you actually tested that?

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-13 : 15:47:50

quote:
Originally posted by lingtrin

It did work, thanks a lot, now I have another question, what if I want to divide (/) this sum by a number? something like:
SUM(CASE WHEN datediff(month,docdate,getdate()) in (0,1,2,3,4,5) THEN TRXQTY ELSE 0 END) as total, total2 = total / 6?

This should do it:

SUM(CASE WHEN datediff(month,docdate,getdate()) in (0,1,2,3,4,5) THEN TRXQTY ELSE 0 END)/6

You may want to divide by a decimal number and round off to the required precision. If you just divide by the integer 6, and TRXQTY is datatype integer, then it will round down to the next whole integer. In other words: 5/6 = 0

CODO ERGO SUM

lingtrin
Starting Member

13 Posts

Posted - 2007-07-13 : 16:38:32

Great
SUM(CASE WHEN datediff(month,docdate,getdate()) in (0,1,2,3,4,5) THEN TRXQTY ELSE 0 END)/6
works, just fine, thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources