SUM of UNION - 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)

SUM of UNION

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-09-19 : 08:45:14

Hey Guru's,

I have:

SELECT ca.Id, ca.category, SUM(o.total) as recs FROM
(
 SELECT p.cmpID, p.categoryID, COUNT(p.categoryID) AS total FROM products p 
 GROUP BY p.cmpID, p.categoryID	
  UNION
 SELECT w.cmpID, w.categoryID, COUNT(w.categoryID) AS total FROM WH_types w
 GROUP BY w.cmpID, w.categoryID
)
o INNER JOIN Categories ca ON ca.Id = o.categoryID INNER JOIN
companyProperties ON o.cmpID = companyProperties.CmpId
WHERE o.cmpID = 160 GROUP BY ca.Id, ca.category

The problem is the counts are wrong when the union tables share a category ID. I presume this is because I group by categoryID.

Is there away around this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-19 : 08:51:10

select ....
(
SELECT 1 as Divider, p.cmpID...
union
SELECT 2 as Divider, p.cmpID...
) t
group by Divider, ...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-09-19 : 08:56:39

As per bloody usual. Feel like a right tit now! Thanks spirit1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 09:01:23

UNION ALL will do just fine

SELECT ca.Id, ca.category, SUM(o.total) as recs FROM
(
 SELECT p.cmpID, p.categoryID, COUNT(p.categoryID) AS total FROM products p 
 GROUP BY p.cmpID, p.categoryID	
  UNION ALL
 SELECT w.cmpID, w.categoryID, COUNT(w.categoryID) AS total FROM WH_types w
 GROUP BY w.cmpID, w.categoryID
)
o INNER JOIN Categories ca ON ca.Id = o.categoryID INNER JOIN
companyProperties ON o.cmpID = companyProperties.CmpId
WHERE o.cmpID = 160 GROUP BY ca.Id, ca.category

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources