Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Add a total to a query

    The query below gives me a table as such:

    Period 1 2 3 4 5 6 7 8 .....12
    2001 0.1 0.1 0.1 0.1 0.2 0.1 0.1 (0.3)
    2000 0.2 (0.0) 0.1 0.1 (0.1) 0.0 (0.0) 0.0
    1999 - - - - 0.0 0.1 0.1 0.1
    1998 0.5 0.2 1.8 1.2 (2.5) (0.9) 0.8 0.5
    1997 1.8 0.7 0.0 (0.1) 3.5 1.2 2.1 1.6
    1996 (1.7) 1.2 (1.0) 0.4 2.0 1.2 (2.6) (2.1)

    I would like to add a total column in the query to sum the colms 1-12 for each year(period)
    I just cant rap my head around this

    SELECT Year([Period]) AS Period,
    Sum(case when Month(Period)=1 then pfr.Rtn else null end) as [1],
    Sum(case when Month(Period)=2 then pfr.Rtn else null end) as [2],
    Sum(case when Month(Period)=3 then pfr.Rtn else null end) as [3],
    Sum(case when Month(Period)=4 then pfr.Rtn else null end) as [4],
    Sum(case when Month(Period)=5 then pfr.Rtn else null end) as [5],
    Sum(case when Month(Period)=6 then pfr.Rtn else null end) as [6],
    Sum(case when Month(Period)=7 then pfr.Rtn else null end) as [7],
    Sum(case when Month(Period)=8 then pfr.Rtn else null end) as [8],
    Sum(case when Month(Period)=9 then pfr.Rtn else null end) as [9],
    Sum(case when Month(Period)=10 then pfr.Rtn else null end) as [10],
    Sum(case when Month(Period)=11 then pfr.Rtn else null end) as [11],
    Sum(case when Month(Period)=12 then pfr.Rtn else null end) as [12]
    FROM ret pfr
    WHERE pfr.Class = @Class
    AND pfr.No = @ID
    GROUP BY Year( [Period]), pfr.Class, pfr. No
    ORDER BY Year( [Period]) DESC

    thanks for the help

  2. #2
    Join Date
    Sep 2010
    Posts
    15
    ;with temp as
    (
    SELECT Year([Period]) AS Period,
    Sum(case when Month(Period)=1 then pfr.Rtn else null end) as [1],
    Sum(case when Month(Period)=2 then pfr.Rtn else null end) as [2],
    Sum(case when Month(Period)=3 then pfr.Rtn else null end) as [3],
    Sum(case when Month(Period)=4 then pfr.Rtn else null end) as [4],
    Sum(case when Month(Period)=5 then pfr.Rtn else null end) as [5],
    Sum(case when Month(Period)=6 then pfr.Rtn else null end) as [6],
    Sum(case when Month(Period)=7 then pfr.Rtn else null end) as [7],
    Sum(case when Month(Period)=8 then pfr.Rtn else null end) as [8],
    Sum(case when Month(Period)=9 then pfr.Rtn else null end) as [9],
    Sum(case when Month(Period)=10 then pfr.Rtn else null end) as [10],
    Sum(case when Month(Period)=11 then pfr.Rtn else null end) as [11],
    Sum(case when Month(Period)=12 then pfr.Rtn else null end) as [12]
    FROM ret pfr
    WHERE pfr.Class = @Class
    AND pfr.No = @ID
    GROUP BY Year( [Period]), pfr.Class, pfr. No
    )
    select *,
    [1]+[2]+...+[12] as total
    from temp
    ORDER BY Year( [Period]) DESC

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    Sweet,, thanks!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Or simply :
    Code:
    SELECT Year([Period]) AS Period,
        Sum(case when Month(Period)=1 then pfr.Rtn else null end) as [1],
        Sum(case when Month(Period)=2 then pfr.Rtn else null end) as [2],
        Sum(case when Month(Period)=3 then pfr.Rtn else null end) as [3],
        Sum(case when Month(Period)=4 then pfr.Rtn else null end) as [4],
        Sum(case when Month(Period)=5 then pfr.Rtn else null end) as [5],
        Sum(case when Month(Period)=6 then pfr.Rtn else null end) as [6],
        Sum(case when Month(Period)=7 then pfr.Rtn else null end) as [7],
        Sum(case when Month(Period)=8 then pfr.Rtn else null end) as [8],
        Sum(case when Month(Period)=9 then pfr.Rtn else null end) as [9],
        Sum(case when Month(Period)=10 then pfr.Rtn else null end) as [10],
        Sum(case when Month(Period)=11 then pfr.Rtn else null end) as [11],
        Sum(case when Month(Period)=12 then pfr.Rtn else null end) as [12],
        Sum(pfr.Rtn) as [YearTotal]
    FROM ret pfr
    WHERE pfr.Class = @Class
        AND pfr.No = @ID
    GROUP BY Year( [Period]), pfr.Class, pfr. No
    ORDER BY Year( [Period]) DESC
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •