How to add sum,convert and datepart to Union

Transact-SQL
1

I have this union market with () below.
I just wonder if it's possible to add SUM(Quantity), SUM(Turnover), SUM(TurnoverTarget), CONVERT(DATE, Datetime), DATEPART(HOUR, Datetime). in the same Union marked with () below

(*)insert INTO [Transform].PosSalesUnion
(DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber, poolID)
SELECT DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber, poolID FROM [Transform].Devices_DeviceGroups_PosSales
UNION
SELECT DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber,NULL FROM [Transform].PosSalesNotMatch

2

UNIONs must all have the same number and types of columns. Thus, you could add it but you'd have to "dummy" out the other columns, something like this, if you want a total for every hour (my best guess based on your original q):

SELECT 0, 0, 0, 0, 0, 0, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, Datetime), 0),
0, SUM(Quantity), SUM(Turnover), SUM(TurnoverTarget), 0, '19000101', '1900010',
NULL, 0
FROM [Transform].Devices_DeviceGroups_PosSales
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, Datetime), 0)

3

You maybe didn't understand what I meant. I want to add SUM(Quantity), SUM(Turnover), SUM(TurnoverTarget), CONVERT(DATE, Datetime), DATEPART(HOUR, Datetime). in the same Union. So you can't remove half of the Union

4

I meant add that query onto the original query:

SELECT ...
UNION
SELECT ...
UNION
SELECT 0, 0, 0, 0, 0, 0, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, Datetime), 0),
0, SUM(Quantity), SUM(Turnover), SUM(TurnoverTarget), 0, '19000101', '1900010',
NULL, 0
FROM [Transform].Devices_DeviceGroups_PosSales
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, Datetime), 0)

1 Like