Post reply

Combine two SELECT statements into one result

  • October 23, 2012 at 8:42 am

    #269957

    SELECT COUNT(tem.ComputerName) AS JavaRecords FROM (SELECT ComputerName FROM v_Java2345 UNION SELECT ComputerName FROM v_Java67) as tem

    SELECT COUNT(*) AS JavaCount FROM

    dbo.v_Java67 j1 WHERE NOT EXISTS (SELECT 1 FROM dbo.v_Java2345 j2 WHERE j1.ComputerName = j2.ComputerName)

    I would like to see the following results into two columns

    JavaRecords JavaCount

    30,000 5,000

    I tried to do a UNION and UNION ALL, it returns the results, however, it does them all in one column.

  • October 23, 2012 at 8:48 am

    #1551952

    A simple trick.

    SELECT JavaRecords, JavaCount

    FROM (

    SELECT COUNT(tem.ComputerName) AS JavaRecords FROM (SELECT ComputerName FROM v_Java2345 UNION SELECT ComputerName FROM v_Java67) as tem) A

    CROSS JOIN (

    SELECT COUNT(*) AS JavaCount FROM

    dbo.v_Java67 j1 WHERE NOT EXISTS (SELECT 1 FROM dbo.v_Java2345 j2 WHERE j1.ComputerName = j2.ComputerName)) B

    This will work OK if you only have one row (as the query is now).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 23, 2012 at 9:12 am

    #1551970

    This works for me when I just want a count of a bunch of stuff....

    Select

    (

    SELECT COUNT(tem.ComputerName) AS JavaRecords FROM (SELECT ComputerName FROM v_Java2345 UNION SELECT ComputerName FROM v_Java67) as tem

    ) JavaRecords

    ,

    (

    SELECT COUNT(*) AS JavaCount FROM

    dbo.v_Java67 j1 WHERE NOT EXISTS (SELECT 1 FROM dbo.v_Java2345 j2 WHERE j1.ComputerName = j2.Comp

    ) JavaCount

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • October 23, 2012 at 9:13 am

    #1551971

    Aaah, CROSS JOIN - that is what I was missing. Thanks so much.

  • October 23, 2012 at 9:14 am

    #1551972

    Thanks, I will save that as well. Appreciate it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply