Clever Geek Handbook

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 with the Case statement. Is this possible, and if so, how can I do it?

Thank!

+3


source to share


2 answers


I think you are trying to find max or sum

employee hours. So something like this should help you. Be aware that columns without an aggregate function must be present in the group.



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
+1


source


You cannot group by column alias in SQL Server - you need to group by 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 cleaner with 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 any case, you need to decide how to aggregate values ​​not included in the group expression. Hours

it makes sense to make a sum, but how would you combine names and ids? If no aggregation is required, just leave them outside 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]
+2


source






More articles:


All Articles