CODEWITHSUNDEEP
sqlsql-server
Michael Robinson
Michael Robinson

Reputation: 1142

How to group by a derived column

I have this SQL:

   Select Hours, LastName, FirstName, UUID, 
      Case 
         When DatePart(WeekDay, Date) = 1 Then
            Date - 6
         Else
            Date - DatePart(Weekday, Date) + 2
         End as [Week]
   From Entry
   Where Date between '06/30/2014' and '10/31/2014'

and what I want to do is group this by the [Week] column that I created using the Case statement. Is this possible and if so, how can I go about doing it?

Thanks!

Upvotes: 3

Views: 2864

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93734

I guess you are trying to find the max or sum of hours of a employee. So something like this should help you. Keep in mind that columns without aggregate function should be present in group by.

SELECT Max(Hours),-- sum(Hours)
       LastName,
       FirstName,
       UUID,
       CASE
         WHEN Datepart(WeekDay, Date) = 1 THEN Date - 6
         ELSE Date - Datepart(Weekday, Date) + 2
       END AS [Week]
FROM   Entry
WHERE  Date BETWEEN '06/30/2014' AND '10/31/2014'
GROUP  BY LastName,
          FirstName,
          UUID,
          CASE
            WHEN Datepart(WeekDay, Date) = 1 THEN Date - 6
            ELSE Date - Datepart(Weekday, Date) + 2
          END

Upvotes: 2

D Stanley
D Stanley

Reputation: 152596

You can't group by a column alias in SQL Server - you need to group by the expression:

Select Hours, LastName, FirstName, UUID, 
  Case 
     When DatePart(WeekDay, Date) = 1 Then
        Date - 6
     Else
        Date - DatePart(Weekday, Date) + 2
     End as [Week]
From Entry
Where Date between '06/30/2014' and '10/31/2014'
GROUP BY
  Case 
     When DatePart(WeekDay, Date) = 1 Then
        Date - 6
     Else
        Date - DatePart(Weekday, Date) + 2
     End

Although you can make it a bit cleaner using a subquery:

SELECT * FROM
   (
   Select Hours, LastName, FirstName, UUID, 
      Case 
         When DatePart(WeekDay, Date) = 1 Then
            Date - 6
         Else
            Date - DatePart(Weekday, Date) + 2
         End as [Week]
    From Entry
    Where Date between '06/30/2014' and '10/31/2014'
    )
    GROUP BY [Week]

But in either case you need to decide how to aggregate the values that are not in the group expression. Hours would make sense to do a sum, but how would you aggregate names and IDs? If no aggregation is necessary just leave them out of the results:

SELECT Week, SUM(Hours) FROM
   (
   Select Hours, LastName, FirstName, UUID, 
      Case 
         When DatePart(WeekDay, Date) = 1 Then
            Date - 6
         Else
            Date - DatePart(Weekday, Date) + 2
         End as [Week]
    From Entry
    Where Date between '06/30/2014' and '10/31/2014'
    )
    GROUP BY [Week]

Upvotes: 3

Related Questions