How to merge two group by queries from the same table?

Question

I have two queries from the same table that I need merged into one table:

SELECT
   
       t.[Origin Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Inbound Shipments',
    
       SUM(t.[Total Revenue]) as 'Inbound Revenue'
     
  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  GROUP BY t.[Origin Terminal Name]

  ORDER BY t.[Origin Terminal Name] ASC


  SELECT
   
       t.[Destination Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Outbound Shipments',
    
       SUM(t.[Total Revenue]) as 'Outbound Revenue'
     
  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  GROUP BY t.[Destination Terminal Name]

  ORDER BY t.[Destination Terminal Name] ASC

They both produce these results:

Terminal    Inbound Shipments   Inbound Revenue
Abilene            520             75970.08
Albany             877            130560.28

Terminal    Outbound Shipments  Outbound Revenue
Abilene             2684           419523.83
Albany              2820           407871.89

I need this as an output:

Terminal    Inbound Shipments  Inbound Revenue  Outbound Shipments  Outbound Revenue
Abilene            520              75970.08           2684              419523.83
Albany             877              130560.28          2820              407871.89

I have tried this query:

 SELECT 
   
       t.[Origin Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Inbound Shipments',
    
       SUM(t.[Total Revenue]) as 'Inbound Revenue',

            (SELECT
   
            t2.[Destination Terminal Name] as 'Terminal',

            COUNT(t2.[Pro Number]) as 'Outbound Shipments',
    
            SUM(t2.[Total Revenue]) as 'Outbound Revenue'
     
            FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t2

            GROUP BY t2.[Destination Terminal Name])
     
  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  LEFT JOIN [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t2 ON t.[Origin Terminal Name] = t2.[Destination Terminal Name]

  GROUP BY t.[Origin Terminal Name]

  ORDER BY t.[Origin Terminal Name] ASC

I received this error:

Msg 116, Level 16, State 1, Line 22 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Is a subquery the way to go to solve this problem? I appreciate any advice

Answer 1

SELECT
   
       t.[Origin Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Inbound Shipments',
    
       SUM(t.[Total Revenue]) as 'Inbound Revenue',

       max(t2.[Outbound Shipments]) as 'Outbound Shipments',

       max(t2.[Outbound Revenue]) as 'Outbound Revenue'

     
  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  INNER JOIN  (SELECT
   
       t2.[Destination Terminal Name] as 'Terminal',

       COUNT(t2.[Pro Number]) as 'Outbound Shipments',
    
       SUM(t2.[Total Revenue]) as 'Outbound Revenue'
     
  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t2

  GROUP BY t2.[Destination Terminal Name]) t2 on t.[Origin Terminal Name] = t2.Terminal

  GROUP BY t.[Origin Terminal Name]

  ORDER BY t.[Origin Terminal Name] ASC