Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Question Unanswered: 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 )

  2. #2
    Join Date
    Nov 2004
    Posts
    6
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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?
    Last edited by blindman; 11-09-04 at 01:37.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Posts
    12

    Thanks!

    Many thanks to you. I knew you guys would knock this out.

    Thanks again.

    Semper Fi,

    JP

  5. #5
    Join Date
    Apr 2004
    Posts
    12

    Thanks!

    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
    Last edited by jjrenner; 11-15-04 at 10:31. Reason: duplicate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •