0

Is it possible to group by multiple columns in a CASE statement? I get error "Incorrect syntax near ','." in below code...

SELECT
  CASE 
     WHEN Type = 'Test'
       THEN Description
     ELSE MAX([Description])                
  END AS 'Description'
GROUP BY
CASE
    WHEN Type = 'Test'
        THEN Description
    ELSE ''
END,
Type, Account, Structure    
2
  • 1
    No, this syntax is invalid. Why doesn't GROUP BY Type, Account, Structure, Description work for you? Nov 4, 2014 at 13:57
  • If 'Type' is a certain value then I want to GROUP differently
    – K09
    Nov 4, 2014 at 14:49

3 Answers 3

6

I guess that when Type has a certain value ('Test'), you want the Structure and Description columns to be considered for the grouping but otherwise to not use them at all. This is one way to do this:

GROUP BY
    Type, Account,
    CASE WHEN Type = 'Test' THEN Structure ELSE '' END, 
    CASE WHEN Type = 'Test' THEN Description ELSE '' END

The '' should be a (any) constant expression that has the same datatype with Structure and Description respectively.

Alternatively, you could separate the 2 cases, which will give you more flexibility on adjusting the SELECT lists:

SELECT 
    ---
FROM
    ---
WHERE
    Type = 'Test'
GROUP BY
    Type, Account, Structure, Description

UNION ALL

SELECT 
    ---
FROM
    ---
WHERE
    (Type <> 'Test' OR Type IS NULL)
GROUP BY
    Type, Account ;
7
  • Issue with using this in my SELECT statement now. I get the error 'Description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
    – K09
    Nov 4, 2014 at 18:10
  • Use CASE WHEN Type = 'Test' THEN Description ELSE '' END instead. Nov 4, 2014 at 18:11
  • Same error I'm afraid.
    – K09
    Nov 4, 2014 at 18:35
  • No, it can't be. Show us the exact query you used. Nov 4, 2014 at 18:36
  • 1
    @k09 Erm...where exactly is the FROM in your new SQL query? Nov 5, 2014 at 9:32
1
SELECT MAX(Description) as MAX_Description,a.Structure ,a.Description  
FROM (select ,CASE WHEN Type = 'Test' THEN Structure 
                   ELSE '' as Structure
              CASE WHEN Type = 'Test' THEN Description 
                   ELSE '' END as Description 
      from <Table_Name> ) a
GROUP BY a.Structure ,a.Description
0

You also have an error in your select clause, which is why you are getting the message you mentioned in your comment. When you are grouping, you can only reference aggregates of your data, or columns in your grouping set. In your case statement that is determining your description, you are referencing a non-aggregated description.

Try just removing the case statement there, and taking the max(description) regardless of type. I think that might get you what you want; If it doesn't, you'll have to explain your data a little more, because what you are trying to do seems backwards.

2
  • When Type = 'Test' I group by Description. So my select query is ... SELECT Description... When Type = 'OtherValues' I DO NOT group by Description. So I need to max/min/sum Description.
    – K09
    Nov 5, 2014 at 8:31
  • Looks like it works fine without needing the CASE in the select statement as the GROUP is done logically before the SELECT... SELECT MAX(Description) AS 'Desc' FROM myTbl GROUP BY CASE WHEN Type = 'Test' THEN Description ELSE '' END, Type, Account, Structure
    – K09
    Nov 5, 2014 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.