Forum Stats

  • 3,826,078 Users
  • 2,260,593 Discussions
  • 7,896,782 Comments

Discussions

Combining two or more select statements and order by based on alias names to get result?

User_X9S6M
User_X9S6M Member Posts: 71 Red Ribbon

SELECT 'TB_SPVC2_STG_FF_DATA_SALES' as TABLE_NAME, COUNT(*)as Record_Count from TB_SPVC2_STG_FF_DATA_SALES

UNION ALL

SELECT 'TB_SPVC2_STG_FF_MFG_PLANT_CNTR' as TABLE_NAME, COUNT(*)as Record_Count from TB_SPVC2_STG_FF_MFG_PLANT_CNTR

UNION ALL

SELECT 'TB_PPM_STG_FF_GEOGRAPHY' as TABLE_NAME, COUNT(*)as Record_Count from TB_PPM_STG_FF_GEOGRAPHY

UNION ALL

SELECT 'TB_SPVC2_STG_FF_COMS_BUY_RESALE' as TABLE_NAME, COUNT(*)as Record_Count from TB_SPVC2_STG_FF_COMS_BUY_RESALE



Actual Result:


TABLE_NAME RECORD_COUNT

TB_PPM_STG_FF_GEOGRAPHY 675

TB_SPVC2_STG_FF_MFG_PLANT_CNTR 448

TB_SPVC2_STG_FF_DATA_SALES 1,178

TB_SPVC2_STG_FF_COMS_BUY_RESALE 197,619



Expected Result :


TABLE_NAME RECORD_COUNT

TB_SPVC2_STG_FF_DATA_SALES 1,178

TB_SPVC2_STG_FF_MFG_PLANT_CNTR 448

TB_PPM_STG_FF_GEOGRAPHY 675

TB_SPVC2_STG_FF_COMS_BUY_RESALE 197,619

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,047 Red Diamond

    Hi, @User_X9S6M

    What is wrong with the results you're getting now? Do you just want the rows in a certain order? If so, add an ORDER BY clause.

    If you want the rows sorted with table_names in this order:

    1. TB_SPVC2_STG_FF_DATA_SALES
    2. TB_SPVC2_STG_FF_MFG_PLANT_CNTR
    3. TB_PPM_STG_FF_GEOGRAPHY
    4. TB_SPVC2_STG_FF_COMS_BUY_RESALE 

    then you can do this:

    WITH  union_results  AS
    (
      SELECT  'TB_SPVC2_STG_FF_DATA_SALES'	as TABLE_NAME
      , 	   COUNT (*)		    		as Record_Count
      ,	   1	  				AS sort_col
      from   TB_SPVC2_STG_FF_DATA_SALES
        UNION ALL
      SELECT  'TB_SPVC2_STG_FF_MFG_PLANT_CNTR'	as TABLE_NAME
      , 	   COUNT (*)			  	as Record_Count
      ,	   2	  				AS sort_col
      from   TB_SPVC2_STG_FF_MFG_PLANT_CNTR
        UNION ALL
      SELECT  'TB_PPM_STG_FF_GEOGRAPHY'		as TABLE_NAME
      , 	    COUNT (*)				as Record_Count
      ,	   3	  				AS sort_col
      from   TB_PPM_STG_FF_GEOGRAPHY
        UNION ALL
      SELECT  'TB_SPVC2_STG_FF_COMS_BUY_RESALE'	as TABLE_NAME
      , 	   COUNT (*)				as Record_Count
      ,	   4	  				AS sort_col
      from   TB_SPVC2_STG_FF_COMS_BUY_RESALE
    )
    SELECT  table_name
    ,	 RECORD_COUNT
    FROM	 union_results
    ORDER BY sort_col
    ;
    


    User_X9S6M

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,047 Red Diamond

    Hi, @User_X9S6M

    What is wrong with the results you're getting now? Do you just want the rows in a certain order? If so, add an ORDER BY clause.

    If you want the rows sorted with table_names in this order:

    1. TB_SPVC2_STG_FF_DATA_SALES
    2. TB_SPVC2_STG_FF_MFG_PLANT_CNTR
    3. TB_PPM_STG_FF_GEOGRAPHY
    4. TB_SPVC2_STG_FF_COMS_BUY_RESALE 

    then you can do this:

    WITH  union_results  AS
    (
      SELECT  'TB_SPVC2_STG_FF_DATA_SALES'	as TABLE_NAME
      , 	   COUNT (*)		    		as Record_Count
      ,	   1	  				AS sort_col
      from   TB_SPVC2_STG_FF_DATA_SALES
        UNION ALL
      SELECT  'TB_SPVC2_STG_FF_MFG_PLANT_CNTR'	as TABLE_NAME
      , 	   COUNT (*)			  	as Record_Count
      ,	   2	  				AS sort_col
      from   TB_SPVC2_STG_FF_MFG_PLANT_CNTR
        UNION ALL
      SELECT  'TB_PPM_STG_FF_GEOGRAPHY'		as TABLE_NAME
      , 	    COUNT (*)				as Record_Count
      ,	   3	  				AS sort_col
      from   TB_PPM_STG_FF_GEOGRAPHY
        UNION ALL
      SELECT  'TB_SPVC2_STG_FF_COMS_BUY_RESALE'	as TABLE_NAME
      , 	   COUNT (*)				as Record_Count
      ,	   4	  				AS sort_col
      from   TB_SPVC2_STG_FF_COMS_BUY_RESALE
    )
    SELECT  table_name
    ,	 RECORD_COUNT
    FROM	 union_results
    ORDER BY sort_col
    ;
    


    User_X9S6M
  • Vojin Janjic
    Vojin Janjic Member Posts: 73 Blue Ribbon

    Have you tried adding another alias (say, SORT_ORDER) that would correspond to the expected result? So, each SQL would look like

    SELECT 1 as SORT_ORDER, 'TB_SPVC2_STG_FF_DATA_SALES' as TABLE_NAME, COUNT(*) as Record_Count

    from TB_SPVC2_STG_FF_DATA_SALES

    GROUP BY SORT_ORDER, TABLE NAME

    If that doesn't work immediately, you can wrap up your union in a simple select * statement where you can use ORDER BY command.

  • User_X9S6M
    User_X9S6M Member Posts: 71 Red Ribbon