July 12, 2007 at 10:42 am
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?
July 12, 2007 at 1:02 pm
If you post the query it might be a little easier, but you should be able to do something akin to...
SELECT SUM(period1) + SUM(period2) + SUM(period3) AS allperiodsums
July 12, 2007 at 3:00 pm
here's my query:
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
What I want to do is to add al the "periodX" toghether and if possible divide it by 6 (percentage)
Thanks
July 24, 2007 at 9:01 am
alvaro,
you can use query in place of a table in another query. I like to do it in similar cases, because it seems to me more understandable and easier to maintain.
Example:
SELECT mysums.item_id, mysums.SumA, mysums.SumB, mysums.SumC, (mysums.SumA+mysums.SumB+mysums.SumC)/3 as average
FROM
(SELECT st.item_id, SUM(st.colA) as SumA, SUM(st.colB) as SumB, SUM(st.colC) as SumC
FROM sometable st
GROUP BY st.item_id) AS mysums
WHERE .....
You can do this even on several levels, if necessary, and you can join to other tables in any of the queries - this was simplified to show the basics.
Remember : all columns in the inner query must have unique names/aliases. Imagine it like you were really creating a new table or view. So, any computed columns will need aliases, as well as any that come from different tables but have the same column name.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply