Adding columns

• alvaro-454996

  SSC Enthusiast

  Points: 115

  More actions

  July 12, 2007 at 10:42 am

  #196853

  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?

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  July 12, 2007 at 1:02 pm

  #719078

  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

• alvaro-454996

  SSC Enthusiast

  Points: 115

  More actions

  July 12, 2007 at 3:00 pm

  #719111

  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

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  July 24, 2007 at 9:01 am

  #721954

  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.