How to group by a calculated column

Question

I need to group by a calculated field in column SQL Server 2005/2008.

I have the following query:

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 mwspp.DateDue
order by mwspp.DateDue

Instead of group by mwspp.DateDue I need to group by the result of the calculation. Is it possible?

Dumpish

Answer 1

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.

Hamza

Thanks, but isn't this a bit expensive? or does sql server somehow optimize it to perform the calculation only once ? – Dumpish

@Jonny, I've added a few thoughts - the best thing to do is give it a shot on your data and see if it's OK - if not, look at other options to improve efficiency at that point. – Hamza

Personally, I would expect the performance of all four answers to be pretty much identical - the main difference is that the approach(es) used by me and Bluefeet are more DRY than the other answers, since we only had to include the calculation once. – Rouse

@MarkBannister, agreed, that was the point I was hoping to make above in the comment after the edit; one would expect the optimiser to treat them equally in terms of phyical operations. So other than it's whatever is most readable/efficient to the user - if I was doing this myself I would consider a CTE for anything more than a trivially complicated scenario. – Hamza

You have to copy-paste the calculation?! However ORDER BY does not require copy-paste. Why? who knows. some obscure internal implementation detail. This is why I dislike SQL. The limitation make no sense and encourage bad practices. – Annihilator

Should the computed column be in the SELECT clause for this to work out? – Kreis

Answer 2

If you want to GROUP BY the dateadd calculation, then you will either need to place that formula in the GROUP BY clause or you can wrap your query in another SELECT:

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

Kiesha

Answer 3

There are several ways of doing so - one involves using a CTE (Common Table Expression):

with cte as (
    select m.*, 
        dateadd(day, -7, Convert(DateTime, DateDue) + (7 - datepart(weekday, DateDue))) datecalc
    from manufacturingweekshortagepartpurchasing m
)
select datecalc, sum(QtyRequired)
from cte
where buildScheduleSimID = 10109 and partID = 8366
group by datecalc
order by datecalc;

Rouse

what does cte stand for? – Gaston

@vy32: CTE stands for "Common Table Expression". – Toughminded

Answer 4

Simply place the calculation in the GROUP BY clause:

Replace

group by mwspp.DateDue

To

group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue)))

Simplex

Answer 5

Adding the CROSS APPLY solution for completeness (improving this question as a duplicate target).

Using CROSS APPLY allows a calculated value to be reused multiple times without needing to repeat the expression.

As stated in other answers, its unlikely to affect performance, more a style choice.

select c.DateCalc, sum(mwspp.QtyRequired)
from ManufacturingWeekShortagePartPurchasing mwspp
cross apply (values (dateadd(day, -7, convert(datetime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue))))) c (DateCalc)
where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366
group by c.DateCalc
order by c.DateCalc;

Inclinatory