How to group by a Calculated Field

by

Sure, just add the same calculation to the GROUP BY clause:

select dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue))),
sum(mwspp.QtyRequired)
from manufacturingweekshortagepartpurchasing mwspp
where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366
group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue)))
order by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue)))

Edit after comment:

Like all questions regarding the optimiser, the answer is really “it depends”, but most likely it will only be performed once – you’ll see this in the execution plan as a Compute Scalar operator.

Based on this Compute Scalar operation, the optimiser will then decide how to perform the actual aggregation.

The other answers here (CTE/subquery, etc) are all equally valid, but don’t really change the logic – ultimately they will be performing similar operations. SQL Server might treat them differently but it’s unlikely. However they will help with readability.

If you’re worried about efficiency you can look at a couple of options, e.g. setting up the calculation as a persisted computed column and using this in an index, or adding interim results into a temporary table.

The only way to really know for sure is to inspect the Execution Plan/IO statistics when running the query on a typical data set and seeing if you’re satisfied with the performance; if not, perhaps investigating one of the above options.

More Related Contents:

  1. How to return a incremental group number per group in SQL
  2. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  3. How do I perform an IF…THEN in an SQL SELECT?
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. Is there a combination of “LIKE” and “IN” in SQL?
  6. SQL query to select dates between two dates
  7. In SQL, how can you “group by” in ranges?
  8. is there an advantage to varchar(500) over varchar(8000)?
  9. Selecting COUNT(*) with DISTINCT
  10. SQL query to split column data into rows
  11. What is the difference between using a cross join and putting a comma between the two tables?
  12. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  13. Pivot using SQL Server 2000
  14. Equivalent of Oracle’s RowID in SQL Server
  15. How to update and order by using ms sql
  16. Dynamic update statement with variable column names
  17. How to store image in SQL Server database tables column [duplicate]
  18. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  19. SQL “between” not inclusive
  20. Unordered results in SQL
  21. Using PIVOT in SQL Server 2008
  22. sql group by only rows which are in sequence
  23. SQL Server replace, remove all after certain character
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  26. When using GETDATE() in many places, is it better to use a variable?
  27. is it possible to select EXISTS directly as a bit?
  28. Using CASE Statement inside IN Clause
  29. SQL Convert Week Number to Date (dd/MM)
  30. How to do a case sensitive GROUP BY?

Leave a Comment