Select with case not included in group by

• lukaszpiech

  SSCommitted

  Points: 1538

  More actions

  November 18, 2019 at 11:21 am

  Go to Answer

  #3698472

  Hello,

  In following query there is a CASE sentence in SELECT clause but it isn't placed in GROUP BY.

  I've two:

  1. Why there is no "group by" error?
  2.  Why results of query WITHOUT CASE in GROUP BY are the same as a query WITH CASE in GROUP BY clause?

  SELECT
     [Names],
     [CardType],
     count(*) as NumberOfRows,
       CASE Names
    WHEN 'A' THEN 'Class 1'
    WHEN 'B' THEN 'Class 2'
    WHEN 'C' THEN 'Class 3'
    WHEN 'D' THEN 'Class 4'
    END as 'Classes'
    FROM [dbo].[_MyBase]
  GROUP BY [Names], [CardType]
  --, CASE Names
  --  WHEN 'A' THEN 'Class 1'
  --  WHEN 'B' THEN 'Class 2'
  --  WHEN 'C' THEN 'Class 3'
  --  WHEN 'D' THEN 'Class 4'
  --  END
  ORDER BY [Names], [CardType]

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 18, 2019 at 11:38 am

  Answer

  #3698477

  (1) Because every non-aggregated column in your SELECT list appears in your GROUP BY clause

  (2) Because the CASE expression doesn't have any effect on the Names, CardType partitions

  John

• Vedat ÖZER

  SSC Enthusiast

  Points: 103

  More actions

  November 18, 2019 at 11:41 am

  #3698478

  Hello,

   

  DECLARE @_MyBase TABLE
  (
  Names NVARCHAR(15),
  CardType INT
  )
  
  INSERT INTO @_MyBase
  VALUES('A','1'),('B','1'),('C','1'),('D','1')
  
  SELECT 
  NAMES,
  CardType
  ,count(*) as NumberOfRows
  , CASE Names
  WHEN 'A' THEN 'Class 1'
  WHEN 'B' THEN 'Class 2'
  WHEN 'C' THEN 'Class 3'
  WHEN 'D' THEN 'Class 4'
  END Classes
  FROM @_MyBase
  GROUP BY [Names], [CardType]

  Allah bize yeter, O ne güzel vekildir.

  vedatoozer@gmail.com

• lukaszpiech

  SSCommitted

  Points: 1538

  More actions

  November 18, 2019 at 12:43 pm

  #3698488

  Thank you very much for your answers!