Need help with UNION and SUM

Hi!

I am trying to join to different queries into one table ( I accomplished this)

Next I need to ADD or SUM the results of 2 rows to form a single row.

As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.

Any help is greatly appreciated!

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
Count(ProdID0) as '# copies installed',
'' as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )

UNION

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
'' as '# copies installed',
Count(ProdID0) as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND
DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )Change your null string place holder to a zero and use a table variable to return only 1 row for each instance of ColA and ColB. For example

DECLARE @.myTable table
(
ColA varchar(50), CoB varchar(50), ColC int, ColD int
)

INSERT INTO @.myTable
(ColA, ColB, ColC, ColD)

SELECT ColA, ColB, SUM(ColC), 0
FROM TableA

UNION ALL

SELECT ColA, ColB, 0, Sum(ColD)
FROM TableB
GROUP BY ColA, ColB

ORDER BY 1, 2

SELECT ColA, ColB, SUM(ColC) AS ColC, SUM(ColD) AS ColD
FROM @.myTable
GROUP BY ColA, ColB
ORDER BY 1, 2
GO|||First, fix your queries to eliminate joining tables in your WHERE clauses.
Second, you don't (and shouldn't) enclose numeric values in quotes.
Third, you don't need UNION here. You need a full outer join:

SELECT COPIESINSTALLED.ProdID0 as 'Product Name', COPIESINSTALLED.SMS_Installed_Sites0 as 'Site', COPIESINSTALLED.TOTAL as '# copies installed', LEGITCOPIESINSTALLED.TOTAL as '# legitimate copies installed'
FROM
(SELECT v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 , Count(ProdID0) TOTAL
FROM v_R_System SYS,
INNER JOIN v_GS_Workstation_Status HWSCAN ON SYS.ResourceId = HWSCAN.ResourceId
INNER JOIN v_gs_SupportedPackages ON SYS.ResourceId = v_gs_SupportedPackages.ResourceId
INNER JOIN v_RA_System_SMSInstalledSites ON v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
GROUP BY v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 ) COPIESINSTALLED
FULL OUTER JOIN
(SELECT v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 , Count(ProdID0) TOTAL
FROM v_R_System SYS,
INNER JOIN v_GS_Workstation_Status HWSCAN ON SYS.ResourceId = HWSCAN.ResourceId
INNER JOIN v_gs_SupportedPackages ON SYS.ResourceId = v_gs_SupportedPackages.ResourceId
INNER JOIN v_RA_System_SMSInstalledSites ON v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= 20
GROUP BY v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 ) LEGITCOPIESINSTALLED
ON COPIESINSTALLED.ProdID0 = LEGITCOPIESINSTALLED.ProdID0
AND COPIESINSTALLED.SMS_Installed_Sites0 = LEGITCOPIESINSTALLED.SMS_Installed_Sites0

Finally, you could probably do the whole thing in a single SELECT with a CASE statement in the result set, but I'm not clear enough on your task to create the SQL for you.

...and one other thing. What is "substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)" supposed to be doing for you?|||Many thanks to you. I knew you guys would knock this out.

Thanks again.

Semper Fi,

JP|||Blindman,

Thanks again for your help. In regards to:

Finally, you could probably do the whole thing in a single SELECT with a CASE statement in the result set, but I'm not clear enough on your task to create the SQL for you.

...and one other thing. What is "substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)" supposed to be doing for you?
__________________

The substring was the only way I could pump in a variable with commas. On our web reports this value is dynamic and due to the naming conventions of our admins, some put commas in the title to separate the different versions of a package.

If you know a better way to capture commas and not have SQL try to evaluate it as an expression, please let me know!

Many thanks again,

JP