CODEWITHSUNDEEP
sqlsql-servert-sql
Chris
Chris

Reputation: 1229

Duplicating group by clause in select

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.

Upvotes: 0

Views: 53

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

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.

Upvotes: 0

dev_b_459821
dev_b_459821

Reputation: 41

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

Upvotes: 0

Related Questions