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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy