Menu
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.
820
The UNION ALL operator can use the ORDER BY clause to order the results of the query in SQL Server (Transact-SQL).
There's no need to run GROUP BY on the contents when you're using UNION - UNION ensures that duplicates are removed; UNION ALL is faster because it doesn't - and in that case you would need the GROUP BY... There are use cases for wanting to use GROUP BY on the results of a union.
Both GROUP BY and ORDER BY are clauses (or statements) that serve similar functions; that is to sort query results. However, each of these serve very different purposes; so different in fact, that they can be employed separately or together.
use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Basically the only time an ORDER in a union will be useful is if you are using LIMIT as well.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
