Group By and Order By with UNION ALL

Question

I have a stored procedure with the following query:

SELECT (sum(addition)) AS [COUNT], 
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102) 
ORDER BY  CONVERT(VARCHAR(12),CREATED,102)

I need to do a union all, so I could get sum of results but from TWO tables, and I want the result to be grouped by and ordered by the same way.

This doesn't work:

SELECT (sum(addition)) AS [COUNT], 
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102) 
ORDER BY  CONVERT(VARCHAR(12),CREATED,102)  

UNION ALL

SELECT (sum(addition)) AS [COUNT], 
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_TWO]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102) 
ORDER BY  CONVERT(VARCHAR(12),CREATED,102)

I want to order the overall result, and group it by the date.

You can only order the overall result of a union, not each individual query. — , Jan 07 '15 at 12:03
@a_horse_with_no_name thats exactly what I want. i was able to order the overall result but i cant group it — HelpASisterOut, Jan 07 '15 at 12:06
Have you tried using an aliased subquery then group by the alias.field_name? — chridam, Jan 07 '15 at 12:11

score 22 · Accepted Answer · edited Nov 30 '17 at 20:30

You could apply the group by and order by after the union all:

SELECT  (SUM(addition)) AS [COUNT], MAX([date]) AS [max_date]
FROM    (SELECT addition, CONVERT(VARCHAR(12),CREATED,102)) as [date]
         FROM   [TABLE_ONE]
         WHERE  CONVERT(VARCHAR(12),CREATED,102) 
                    BETWEEN CONVERT(date,@startdate) AND 
                            CONVERT(date,@enddate)
                AND [ServiceID]=@serid 
         UNION ALL
         SELECT addition, (CONVERT(VARCHAR(12),CREATED,102)) as [date]
         FROM   [TABLE_TWO]
         WHERE  CONVERT(VARCHAR(12),CREATED,102) 
                    BETWEEN CONVERT(date,@startdate) AND 
                            CONVERT(date,@enddate)
                AND [ServiceID]=@serid) t
GROUP BY [date]
ORDER BY 2

I tried that of course. But got Invalid column name 'date'. – HelpASisterOut Jan 07 '15 at 12:31 — HelpASisterOut, Jan 07 '15 at 12:31

Group By and Order By with UNION ALL

1 Answers1