Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Can I use 'CASE' in an aggregate query ?

    select entityid, sum(legcramt), sum(credtamt), max(yrgiving)
    from tb945
    where uses = '0007'
    group by entityid
    having (sum(legcramt) >= 2500 or sum(credtamt) >= 2500)
    or (sum(legcramt) >= 1000 and sum(legcramt) < 2500)
    or (sum(credtamt) >= 1000 and sum(credtamt) < 2500)
    order by entityid


    I would like to assign a value to a amount indicator for each of the 3 conditions in the having clause in the query above. Can I use CASE to achieve this?
    EX. if the sum amt is >= 2500 I would like to put 'G' in the amount indicator. If the sum amt is between 1000 and 2500 then I would like to put "L" in the amount indicator, so on and so forth..

    Thank you so much for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes
    Code:
    SELECT entityid
         , SUM(legcramt)
         , SUM(credtamt)
         , MAX(yrgiving)
         , CASE WHEN SUM(legcramt) >= 2500 
                  OR SUM(credtamt) >= 2500
                THEN 'G' 
                WHEN SUM(legcramt) >= 1000 
                 AND SUM(legcramt)  < 2500
                THEN 'L' 
                WHEN SUM(credtamt) >= 1000 
                 AND SUM(credtamt)  < 2500
                THEN 'X' 
                ELSE NULL END AS Amount_Indicator
      FROM tb945 
     WHERE uses = '0007'
    GROUP 
        BY entityid
    HAVING Amount_Indicator IS NOT NULL
    ORDER 
        BY entityid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    55
    Thank you so much, r937

Posting Permissions

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