Sum of query for different dates but same group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum of query for different dates but same group

Author

Topic

DennisDG00
Starting Member

4 Posts

Posted - 2011-09-22 : 12:13:53

thanks in advance.
I am use sql 2005. I have a query that returns a sum of # of items sold by an employee for a specific date range. I am trying to use Union All to return the results as a sum of the date range. Here is the query I have written however it returns the data in the same column alias [#eft] and not as 2 columns. Can this be done and if so what am I doing wrong? want it to look like this below.

name eft1 eft2
J doe 6 2
a don 2 3

SELECT EmployeeName, SUM(Reg_Transactions.quantity) as [#eFT]
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2011-06-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2011-07-01 00:00:00', 102)) AND
(Subitem LIKE N'%vemma auto%') and (NOT (Reg_Transactions.subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (reg_transactions.Deleted = 0) AND (reg_transactions.Refunded = 0)
UNION ALL
SELECT EmployeeName, SUM(Quantity) AS [eft2]
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2011-07-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2011-08-01 00:00:00', 102)) AND
(Subitem LIKE N'%vemma auto%') AND (NOT (Subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (Deleted = 0) AND (Refunded = 0)
ORDER BY EmployeeName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 12:18:07

[code]SELECT EmployeeName,SUM(efT1) AS eft1,SUM(eft2) AS eft2
FROM
(
SELECT EmployeeName, SUM(Reg_Transactions.quantity) as [eFT1],0.00 AS [eft2]
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2011-06-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2011-07-01 00:00:00', 102)) AND
(Subitem LIKE N'%vemma auto%') and (NOT (Reg_Transactions.subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (reg_transactions.Deleted = 0) AND (reg_transactions.Refunded = 0)
UNION ALL
SELECT EmployeeName,0.00, SUM(Quantity)
FROM Reg_Transactions
WHERE (DateofSale > CONVERT(DATETIME, '2011-07-01 00:00:00', 102)) AND (DateofSale < CONVERT(DATETIME, '2011-08-01 00:00:00', 102)) AND
(Subitem LIKE N'%vemma auto%') AND (NOT (Subitem = N'EFT DRAFT SINGLE UPGRADE'))
GROUP BY EmployeeName, Refunded, Deleted
HAVING (Deleted = 0) AND (Refunded = 0)
)t
GROUP BY EmployeeName
ORDER BY EmployeeName
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DennisDG00
Starting Member

4 Posts

Posted - 2011-09-22 : 12:29:06

Thank you visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 13:04:04

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources