Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Help w/ Sum / Case statement

    I am trying to use a case statement to w/ Sum. The problem is when I run this query, I get an error that I need to use group by. I don't want to use group by b/c all I care about is the Sum of "paycheck".

    If you look at the statement below I'm sure you are rolling your eyes When this is finished I will have 20 more case statements in there, each w/ a different city, state and hours. I'm trying to avoid having those conditions in the Where clause b/c I don't want to have to union the same query 20 times. I really want to have the conditions controlled by the case statements


    SELECT CASE WHEN (hours >= 35 and City = 'NYC' and State = 'NY' and hire between '01-07-2004' and '30-06-2005') THEN Sum(Paycheck) end
    From MyTable
    WHERE [too complicated to put in]

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    sum(case when ... then paycheck else 0 end)

    Alan

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    That got rid of the need for Group By...but how do I get the sum. Right now I get one column of 234 rows. My goal is to have one column w/ a grand total.

    Thanks!

    Quote Originally Posted by AlanP
    try

    sum(case when ... then paycheck else 0 end)

    Alan

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    post the whole sql statement

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select sum(x.amt)
    from
    (SELECT CASE WHEN (hours >= 35 and City = 'NYC' and State = 'NY' and hire between '01-07-2004' and '30-06-2005') THEN Paycheck end amt
    From MyTable
    WHERE [too complicated to put in] ) x;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I dont get how you get 234 rows returned with the query you posted, it looks like it should return one row unless you do have a group by in there.

    Alan
    Last edited by AlanP; 04-19-06 at 18:50.

Posting Permissions

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