CODEWITHSUNDEEP
sql-servert-sql
Ragmar
Ragmar

Reputation: 350

Group by with multiple cases

It is possible to put multiple Group By clauses in the same Select? I'm doing a store procedure and want to return the value of a range of days. If this range of days surpass a quantity, then I want to group by the months, and if this months return a qunatity greater than a year, I want to return the names of the years.

The data is just the current ID of the table, the ID of another table with multiple dates values, all dates are unique withing the other table ID.

I just want to return all the dates of that external table summaries. 

SELECT 
    SUM(D.VALUE) SUM_VALUE,
    CASE
        WHEN DATEDIFF(month,MIN(D.DATE_FIELD),MAX(D.DATE_FIELD)) > 12 THEN YEAR(D.DATE_FIELD)
        WHEN DATEDIFF(week,MIN(D.DATE_FIELD),MAX(D.DATE_FIELD)) > 12 THEN DATEPART(WK, D.DATE_FIELD) + ' ' + YEAR(D.DATE_FIELD)
        ELSE DATEPART(DAY, D.DATE_FIELD) + ' ' + DATEPART(WK, D.DATE_FIELD) + ' ' + YEAR(D.DATE_FIELD)
    END AS NAME

    FROM VALUE_DATE_TABLE D
    WHERE D.ANOTHER_TABLE_ID = @some_ID
    GROUP BY (
            YEAR(D.DATE_FIELD) 
        HAVING
            DATEDIFF(month,MIN(D.DATE_FIELD),MAX(D.DATE_FIELD)) > 12
        OR
             DATEPART(WK, D.DATE_FIELD), YEAR(D.DATE_FIELD)
        HAVING 
            DATEDIFF(week,MIN(D.DATE_FIELD),MAX(D.DATE_FIELD)) > 12
        OR 
            DATEPART(DAY, D.DATE_FIELD), DATEPART(WK, D.DATE_FIELD), YEAR(D.DATE_FIELD))

I'm trying to do the multiple groups by.

The idea would be doing these 3 groups by in the select

Group by YEAR(D.DATE_FIELD)
Group by DATEPART(WK, D.DATE_FIELD), YEAR(D.DATE_FIELD)
Group by DATEPART(DAY, D.DATE_FIELD), DATEPART(WK, D.DATE_FIELD), YEAR(D.DATE_FIELD)

Upvotes: 1

Views: 48

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

You could use GROUP BY GROUPING SETS():

SELECT 
  SUM(D.VALUE) SUM_VALUE,
  CASE
    WHEN DATEDIFF(month,MIN(D.DATE_FIELD),MAX(D.DATE_FIELD)) > 12 
    THEN YEAR(D.DATE_FIELD)
    WHEN DATEDIFF(week,MIN(D.DATE_FIELD),MAX(D.DATE_FIELD)) > 12 
    THEN DATEPART(WK, D.DATE_FIELD) + ' ' + YEAR(D.DATE_FIELD)
    ELSE DATEPART(DAY, D.DATE_FIELD) + ' ' + DATEPART(WK, D.DATE_FIELD) 
         + ' ' + YEAR(D.DATE_FIELD)
    END AS NAME
FROM VALUE_DATE_TABLE D
WHERE D.ANOTHER_TABLE_ID = @some_ID
GROUP BY GROUPING SETS (
  (YEAR(D.DATE_FIELD))
  ,(DATEPART(WK, D.DATE_FIELD), YEAR(D.DATE_FIELD))
  ,(DATEPART(DAY, D.DATE_FIELD), DATEPART(WK, D.DATE_FIELD), YEAR(D.DATE_FIELD))
 );

EDIT:

This seens the right way, but how do I tell the sql which grouping set I'm using

You could use GROUPING_ID:

Is a function that computes the level of grouping. GROUPING_ID can be used only in the SELECT list, HAVING, or ORDER BY clauses when GROUP BY is specified.

Upvotes: 1

Related Questions