Duplicating group by clause in select

Question

Is there a way to not duplicate the group by clause when it is included in the select?

For example, one can do:
Select x,y,z from mytable order  by 1,2,3
If the clauses of x,y, and z are long complicated functions, this saves typing and mistakes.
However, I am not aware of a typing saver for:
Select f(x),g(y),avg(z) from MyTable group by f(x),g(y)

Any ideas?

Larger example:

SELECT 
     DATEADD(HOUR,datepart(hour,inquirydate),cast(cast(inquirydate as date) as datetime)) as dayhour,
     COUNT(*) as qty,
     AVG(workms+queuems+0.0) as avgTimeMs
    FROM datalog 
  WHERE inquirydate>'1/1/2014'
  GROUP BY DATEADD(HOUR,datepart(hour,inquirydate),cast(cast(inquirydate as date) as datetime))

Notice a large chunk of the above was repeated. When one repeats oneself, they tend to make mistakes.

A typing saver? To what end? To save 8 seconds? Put your complicated expression in a CTE or subquery, then outside of that you can select and group by the alias. — Aaron Bertrand, Mar 25 '15 at 21:26
Aaron, is that the typical pattern you follow? Or would you just write the DATEADD query above as I did? — Chris, Mar 25 '15 at 21:32
Yes, I would put the expression in a CTE, and then do grouping/aggregation outside. — Aaron Bertrand, Mar 25 '15 at 21:33
The syntax is clearly SQL Server so I've removed the extraneous daabase tags. — Gordon Linoff, Mar 25 '15 at 21:48

score 0 · Answer 1 · answered Mar 25 '15 at 21:35

0

1) Use a User defined function if this is a common calculation
OR
2) As Aaron Bertrand suggested wrap it in a subquery

answered Mar 25 '15 at 21:35

dev_b_459821

41
1
1
3

score 0 · Accepted Answer · answered Mar 25 '15 at 22:30

As mentioned, there are few methods: subquery, CTE and OUTER APPLY:

-- Solution #1: subquery

SELECT 
    h.dayhour,
    COUNT(*) as qty,
    AVG(workms+queuems+0.0) as avgTimeMs
FROM (
    SELECT 
        DATEADD(HOUR,datepart(hour,inquirydate),cast(cast(inquirydate as date) as datetime)) as dayhour,
        workms, queuems
    FROM    datalog 
    WHERE   inquirydate>'20140101' -- It's better to use a date literal (DDMMYYY for DATETIME, DD-MM-YYYY for DATE) which is independent of DATEFORMAT / LANGUAGE settings
) h -- hours subquery
GROUP BY h.dayhour;

-- Solution #2: Common Table Expression

;WITH CteHours
AS (
    SELECT 
        DATEADD(HOUR,datepart(hour,inquirydate),cast(cast(inquirydate as date) as datetime)) as dayhour,
        workms, queuems
    FROM    datalog 
    WHERE   inquirydate>'20140101' 
)
SELECT h.dayhour,
    COUNT(*) as qty,
    AVG(workms+queuems+0.0) as avgTimeMs
FROM CteHours h -- hours subquery
GROUP BY h.dayhour;

-- Solution #2: OUTER APPLY

SELECT 
    expr.dayhour, 
    COUNT(*) as qty,
    AVG(workms+queuems+0.0) as avgTimeMs
FROM    datalog d
OUTER APPLY (
    SELECT DATEADD(HOUR,datepart(hour,d.inquirydate),cast(cast(d.inquirydate as date) as datetime)) as dayhour
) AS expr
WHERE   inquirydate>'20140101' 
GROUP BY expr.dayhour;

Usually, I use solution #1 and, sometimes (when query is complex), solution #3.

Bogdan Sahlean, the OUTER APPLY looks to be the most concise. Is that standard SQL? I wonder if it will work in PostgresSQL when I need to do that... — Chris, Mar 26 '15 at 00:54
`OUTER/CROSS APPLY` is not included in SQL ANSI standard. According to [this answer](http://stackoverflow.com/questions/11472790/analogue-to-cross-apply) the equivalent of `OUTER APPLY` for `PostgreSQL` is `LATERAL JOIN`. — Bogdan Sahlean, Mar 26 '15 at 04:42

Duplicating group by clause in select

2 Answers2