Post reply

putting two select statements together as two columns in a resultset

  • February 12, 2009 at 10:09 am

    #134670

    Hi

    I want to take the following two select statements together:

    SELECT COUNT(*) AS AM

    FROM dwh_test.dim_employees

    WHERE

    race_code = '01' AND gender_code = '1'

    **************************************************

    SELECT COUNT(*) AS CM

    FROM dwh_test.dim_employees

    WHERE

    race_code = '04' AND gender_code = '1'

    to give me the following result:

    AM CM

    1234 7809

    any ideas???

  • February 12, 2009 at 10:28 am

    #943031

    it's suprisinglyy easy... you just need to use the two tables as aliased subselects:

    SELECT ALIAS1.AM,ALIAS2.CM

    FROM

    (SELECT COUNT(*) AS AM

    FROM dwh_test.dim_employees

    WHERE

    race_code = '01' AND gender_code = '1'

    )ALIAS1,

    (SELECT COUNT(*) AS CM

    FROM dwh_test.dim_employees

    WHERE

    race_code = '04' AND gender_code = '1'

    )ALIAS2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 12, 2009 at 10:33 am

    #943037

    thank you

  • February 12, 2009 at 10:39 am

    #943040

    another way to do it wiht a CASE statement, if you need to do lots of different race codes:

    SELECT

    SUM(CASE WHEN race_code = '01' THEN 1 ELSE 0 END) AS AM ,

    SUM(CASE WHEN race_code = '04' THEN 1 ELSE 0 END) AS CM

    FROM dwh_test.dim_employees

    WHERE gender_code = '1'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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