Geek Answers Handbook

Grouping and ordering with UNION ALL

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 union all , so I could get the sum of the results, but from the TWO tables , and I want the result to be grouped and ordered in the same way.

This does not 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 a general result and group it by date.

+7
sql sql-server-2008 sql-order-by group-by union-all
source share
1 answer

You can apply group by and order by after 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
+11
source share

More articles:


All Articles