CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
variable
variable

Reputation: 9724

SQL Group by with string concat and CASE statements

I am trying to understand SQL Group By concept along with the Select statement having:

  1. String concat operation
  2. CASE statement

I understand the Select clause is evaluated after the Group by clause and that any non aggregated columns in the Select list also need to be present in the Group By list. I will try to explain my questsion with an example ORDERS table that has one record for each order:

SELECT FIRSTNAME +' '+LASTNAME
,EMP_TITLE
,EMAILADDR
,INACTIVE
,CASE WHEN ISNULL(EMAILADDR,'')='' THEN '[email protected]' ELSE EMAILADDR END AS ALTEMAIL
,CASE WHEN AGE>60 THEN 'SENIOR' ELSE 'EMP' END AS EMPTYPE
,COUNT(*) AS TOTAL_ORDERS
FROM ORDERS
GROUP BY FIRSTNAME + ' '+ LASTNAME
,EMP_TITLE
,EMAILADDR
,INACTIVE
,CASE WHEN ISNULL(EMAILADDR,'')='' THEN '[email protected]' ELSE EMAILADDREND
,CASE WHEN AGE>60 THEN 'SENIOR' ELSE 'EMP' END

I understand the Select clause is evaluated after the Group by clause. What I am confused/trying to get my head around is - in what situations do we use the column name vs the contents from the select statement (concat/case in this example) in the group by clause?

Example: In group by above, we could get rid of FIRSTNAME + ' '+ LASTNAME and replace it with FIRSTNAME,LASTNAME. Also we could replace the CASE statements with the EMAILADDR, AGE.

Question:

  1. I am trying to understand in which situations this is OK to do and in which situations is it absolutely necessary that we must place the CASE statement (or the concat string operation) from select clause into the Group By clause?
  2. In situations where it is OK to use either, what is the best practice to follow? Use the case statement as is from select in the group by? Or only use column names involved in the case statement?

Upvotes: 0

Views: 420

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

The GROUP BY clause defines expressions that can be referenced without aggregation in the SELECT. So, this is allowed:

SELECT FIRSTNAME + ' ' + LASTNAME
. . . 
GROUP BY FIRSTNAME, LASTNAME

because FIRSTNAME and LASTNAME can be referenced without aggregation functions. So, any expressions are allowed as well.

On the other hand, this is not allowed:

SELECT FIRSTNAME, LASTNAME
. . . 
GROUP BY

In this case, FIRSTNAME and LASTNAME are not in the GROUP BY, so they are not allowed. This is also allowed:

SELECT HONORIFIC + ' ' + FIRSTNAME + ' ' + LASTNAME 
. . . 
GROUP BY FIRSTNAME + ' ' + LASTNAME, HONORIFIC

However, this is not allowed:

SELECT HONORIFIC + ' ' + FIRSTNAME + ' ' + LASTNAME 
. . . 
GROUP BY FIRSTNAME + ' ' + LASTNAME + HONORIFIC

Upvotes: 1

Related Questions