Union & Group By - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Union & Group By

Author

Topic

HGClubTec
Starting Member

12 Posts

Posted - 2009-12-20 : 22:34:54

I am trying to sum a couple of fields and I'm using a union to get information from a history table. Is there a way to sum the fields from the Union? Here's what I currently have that doesn't do that - but does give me the data I want.

SELECT
E.ID,
E.Number + '-' + E.NumberSuffix AS FullNumber,
E.LastName,
TH.RecipMemberNumber,
TH.RecipFirstName,
TH.RecipLastName,
THH.LocationName,
--TM.BatchTypeENUM,
SUM(TH.TicketTotal) AS TicketTotal,
SUM(TH.TaxTotal) AS TaxTotal,
SUM(TH.ServiceChargeTotal) AS SCTotal,
SUM(TH.TicketTotal) + SUM(TH.TaxTotal) + SUM(TH.ServiceChargeTotal) AS Extension,
T.Code,
T.Description

FROM
ctcreceivables.dbo.TransHeader TH
LEFT OUTER JOIN pos2000.dbo.TicketHeaderHistory THH ON TH.TicketHeaderID = THH.TicketHeaderID
LEFT OUTER JOIN ctcreceivables.dbo.TransMain TM ON TM.ID = TH.TransMainID
LEFT OUTER JOIN ctcpeople.dbo.Entity E ON TH.EntityID = E.ID
LEFT OUTER JOIN ctcpeople.dbo.Type T ON E.TypeID = T.ID

WHERE
T.TypeLevelEnum = 2
AND CONVERT(nvarchar, TH.TransDate, 101) >= @StartDate
AND CONVERT (nvarchar, TH.TransDate, 101) <= @EndDate
AND TM.BatchTypeENUM <> 4

Group By E.ID, E.Number, E.NumberSuffix, E.LastName, TH.RecipMemberNumber, TH.RecipFirstName,
TH.RecipLastName, THH.LocationName, T.Code, T.Description

UNION

SELECT
E.ID,
E.Number + '-' + E.NumberSuffix AS FullNumber,
E.LastName,
TH.RecipMemberNumber,
TH.RecipFirstName,
TH.RecipLastName,
THH.LocationName,
--TM.BatchTypeENUM,
SUM(TH.TicketTotal) AS TicketTotal,
SUM(TH.TaxTotal) AS TaxTotal,
SUM(TH.ServiceChargeTotal) AS SCTotal,
SUM(TH.TicketTotal) + SUM(TH.TaxTotal) + SUM(TH.ServiceChargeTotal) AS Extension,
T.Code,
T.Description

FROM
ctcreceivables.dbo.TransHeaderHistory TH
LEFT OUTER JOIN pos2000.dbo.TicketHeaderHistory THH ON TH.TicketHeaderID = THH.TicketHeaderID
LEFT OUTER JOIN ctcreceivables.dbo.TransMainHistory TM ON TH.TransMainID = TM.TransMainID
LEFT OUTER JOIN ctcpeople.dbo.Entity E ON TH.EntityID = E.ID
LEFT OUTER JOIN ctcpeople.dbo.Type T ON E.TypeID = T.ID

WHERE
T.TypeLevelEnum = 2
AND CONVERT(nvarchar, TH.TransDate, 101) >= @StartDate
AND CONVERT(nvarchar, TH.TransDate, 101) <= @EndDate
AND TM.BatchTypeENUM <> 4

Group By E.ID, E.Number, E.NumberSuffix, E.LastName, TH.RecipMemberNumber, TH.RecipFirstName,
TH.RecipLastName, THH.LocationName, T.Code, T.Description

ORDER BY FullNumber, LocationName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-20 : 22:39:52

You could put the entire thing into a derived table so that you can sum it. Here's an example with t as the derived table:

SELECT SUM(Column1)
FROM (SELECT Column1 FROM Table1 UNION ALL SELECT Column1 FROM Table2) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-20 : 23:28:17

Not sure what you query is doing, but since you are using LEFT JOIN to the table TransMainHistory and Type, you might want to place the 2 condition highlighted in RED at the join condition rather than WHERE clause. Placing it in the WHERE clause will change the effect of the LEFT JOIN to a INNER JOIN.


FROM
ctcreceivables.dbo.TransHeaderHistory TH
LEFT OUTER JOIN pos2000.dbo.TicketHeaderHistory THH ON TH.TicketHeaderID = THH.TicketHeaderID
LEFT OUTER JOIN ctcreceivables.dbo.TransMainHistory TM ON TH.TransMainID = TM.TransMainID
LEFT OUTER JOIN ctcpeople.dbo.Entity E ON TH.EntityID = E.ID
LEFT OUTER JOIN ctcpeople.dbo.Type T ON E.TypeID = T.ID

WHERE
T.TypeLevelEnum = 2
AND CONVERT(nvarchar, TH.TransDate, 101) >= @StartDate
AND CONVERT(nvarchar, TH.TransDate, 101) <= @EndDate
AND TM.BatchTypeENUM <> 4

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources