none
Display values from several SELECTs as 1 row?

    Question

  • I have several SELECT COUNT(*) statements that compare two tables.

    Without using temp tables, instead of displaying the following as rows, how can I display them as columns?

    Any help is appreciated.

    Thanks.

    select count(*) From s1 left join s2 on s1.field = s2.field where s2.field is not null
    UNION
    select count(*) From s1 left join s2 on s1.field = s2.field where s2.field is null
    union
    select count(*) From s1 left join s3 on s1.field = s3.field where s3.field is not null
    UNION
    select count(*) From s1 left join s2 on s1.field = s3.field where s3.field is null


    VM

    Tuesday, June 25, 2013 8:37 PM

Answers

  • "milder mannered" is one way of putting it. I'm just glad he didn't show up here.

    Anyways, I ended up separating all SELECTs. Considering that s2 and s3 are not related, I didn't think there's a need to do the 2nd left join.

    I ended up doing this:

    ;with cte as 
    (
    select count(*) s2IsNotNull From s1 left join s2 on s1.field = s2.field where s2.field is not null
    ), 
    cte2 as
    (
    select count(*) s2IsNull From s1 left join s2 on s1.field = s2.field where s2.field is null
    )
    , cte3 as
    (
    select count(*) s3IsNotNull From s1 left join s3 on s1.field = s3.field where s3.field is not null
    )
    select s2IsNotNull, s2IsNull, s3IsNotNull
    from cte cross join cte2 cross join cte3



    VM


    • Edited by vmhatup Tuesday, June 25, 2013 9:48 PM sql change
    • Marked as answer by vmhatup Tuesday, June 25, 2013 9:49 PM
    Tuesday, June 25, 2013 9:47 PM

All replies

  • select count(s2.field) s2IsNotNull
    count(*) - count(s2.field) s2IsNull, 
    count(s3.field) s3IsNotNull
    count(*) - count(s3.field) s3IsNull,
    From s1 left join s2 on s1.field = s2.field  
            left join s3 on s1.field = s3.field
    ;
    -- Alternate if field is not a key:
    ; with s2Count as (
    select count(s2.field) s2IsNotNull,
    count(*) - count(s2.field) s2IsNull
    From s1 left join s2 on s1.field = s2.field  
    ) ,
    s3Count as (
    select count(s3.field) s3IsNotNull,
    count(*) - count(s3.field) s3IsNull
    From s1 left join s3 on s1.field = s3.field
    )
    select * from s2Count cross join s3Count


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, June 25, 2013 8:59 PM
  • Thanks for the post.

    I noticed that you're subtracting one from the other, but the queries are really unrelated.

    Is it possible that they remain separate?


    VM

    Tuesday, June 25, 2013 9:07 PM
  • To paraphrase one of our milder mannered members, you really need to give ddl for what you are trying to get and a better filled in designed.

    I subtract because that is the easiest way to find nulls in a column.  You count all rows then subtract the count of the column, which eliminates the nulls.

    I really think that if you have a job with SQL, you can figure out from my query the answer to your last question.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, June 25, 2013 9:24 PM
  • "milder mannered" is one way of putting it. I'm just glad he didn't show up here.

    Anyways, I ended up separating all SELECTs. Considering that s2 and s3 are not related, I didn't think there's a need to do the 2nd left join.

    I ended up doing this:

    ;with cte as 
    (
    select count(*) s2IsNotNull From s1 left join s2 on s1.field = s2.field where s2.field is not null
    ), 
    cte2 as
    (
    select count(*) s2IsNull From s1 left join s2 on s1.field = s2.field where s2.field is null
    )
    , cte3 as
    (
    select count(*) s3IsNotNull From s1 left join s3 on s1.field = s3.field where s3.field is not null
    )
    select s2IsNotNull, s2IsNull, s3IsNotNull
    from cte cross join cte2 cross join cte3



    VM


    • Edited by vmhatup Tuesday, June 25, 2013 9:48 PM sql change
    • Marked as answer by vmhatup Tuesday, June 25, 2013 9:49 PM
    Tuesday, June 25, 2013 9:47 PM