locked
Calculate Passengers on each Destination RRS feed

  • Question

  • Hello Experts,

    I am trying to calculate the number of passengers traveled to each destination. Here is some sample code, if that helps.

    DDL:

    CREATE TABLE #PAX
    (
    SEG INT,
    DEP VARCHAR(3),
    ARR VARCHAR(3),
    First_Class INT,
    Economy INT
    )
    DML:
    INSERT INTO #PAX(SEG, DEP, ARR, First_Class, Economy)
    SELECT 1, 'STL', 'JFK', 11, 166
    UNION ALL
    SELECT 2, 'STL', 'DXB', 13, 21
    UNION ALL
    SELECT 3, 'JFK', 'DXB', 11, 150
    ;

    Apparently, I would like to know how many passengers have flown to JFK and DXB.

    Expected Result:

    SELECT 'STL', 'JFK', 24, 187
    UNION ALL
    SELECT 'JFK', 'DXB', 24, 171

    Let me know if you need anything else.

    Thanks!


    Known is a DROP, Unknown is an OCEAN.

    Thursday, November 28, 2019 5:23 PM

Answers

  • Hi Bangaaram,

    Please try my script.

    --drop table #PAX
    --go
    CREATE TABLE #PAX
    (
    SEG INT,
    DEP VARCHAR(3),
    ARR VARCHAR(3),
    First_Class INT,
    Economy INT
    )
    INSERT INTO #PAX(SEG, DEP, ARR, First_Class, Economy)
    SELECT 1, 'STL', 'JFK', 11, 166
    UNION ALL
    SELECT 2, 'STL', 'DXB', 13, 21
    UNION ALL
    SELECT 3, 'JFK', 'DXB', 11, 150
    
    ;with cte as (
    select SEG, DEP, ARR,
    sum (First_Class)over(partition by DEP)  First_Class1,
    sum (Economy)over(partition by DEP)  Economy1,
    sum (First_Class)over(partition by ARR)  First_Class2,
    sum (Economy)over(partition by ARR)  Economy2
    from #PAX
    )
    select DEP,max(ARR) ARR ,
    case when max(First_Class1)>max(First_Class2) then max(First_Class1) else max(First_Class2) end   First_Class,
    case when max(Economy1)>max(Economy2) then max(Economy1) else max(Economy2) end   Economy
    from cte
    group by DEP
    /*
    DEP  ARR  First_Class Economy
    ---- ---- ----------- -----------
    JFK  DXB  24          171
    STL  JFK  24          187
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 29, 2019 5:50 AM

All replies

  • >> calculate the number of passengers traveled to each destination. Here is some sample code, if that helps.<<

    It's not just helpful; it's vital. You posted garbage. First of all, by definition, a table must have a key, but what you posted. Can never have a key. Because none of the columns are not null. You seem to be an old basic or Fortran programmer because you use insanely short column names and use an uppercase (the only reason for using uppercase all the time today is that you're still on the keypunch machine from the 1950s).

    Please buy any book on basic data modeling before you post again. Things like "first class", etc. our values in the category scale. They are not attributes! Do you understand the difference between a value and an attribute? I'm going to guess that you meant for those numbers to be a count of flights on those flight segments. I wish you would learn to tell us these things.

    Here's an attempt at fixing up your non-table

    CREATE TABLE Trips 
    (trip_segment CHAR(5) NOT NULL PRIMARY KEY,
    departure_airport_code CHAR(3) NOT NULL,
    arrival_airport_code CHAR(3) NOT NULL,
     trip_class VARCHAR(15) NOT NULL
         CHECK (trip_class IN ('First_Class', 'Economy'),
     flight_cnt INTEGER NOT NULL CHECK (flight_cnt >0));

    INSERT INTO Trips
    VALUES
    ('001', 'STL', 'JFK', 'First Class', 11)
    ('001', 'STL', 'JFK', 'Economy',  166)
    ('002', 'STL', 'DXB', 'Economy', 21),
    ('002', 'STL', 'DXB', 'First Class', 13),
     etc. 

    >> I would like to know how many passengers have flown to JFK and DXB. <<

    SELECT SUM(flght_cnt) AS flight_cnt_tot
      FROM Trips
    WHERE arrival_airport_code IN ('JFK', 'DXB');

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, November 28, 2019 9:01 PM
  • I can see that the row with STL and JFK is the result of SEG  1+2, and likewise that the row with JFK + DXB is SEG 2+3. But what determines that you want exactly these rows and with these key values?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, November 28, 2019 10:50 PM
  • Hi Bangaaram,

    Please try my script.

    --drop table #PAX
    --go
    CREATE TABLE #PAX
    (
    SEG INT,
    DEP VARCHAR(3),
    ARR VARCHAR(3),
    First_Class INT,
    Economy INT
    )
    INSERT INTO #PAX(SEG, DEP, ARR, First_Class, Economy)
    SELECT 1, 'STL', 'JFK', 11, 166
    UNION ALL
    SELECT 2, 'STL', 'DXB', 13, 21
    UNION ALL
    SELECT 3, 'JFK', 'DXB', 11, 150
    
    ;with cte as (
    select SEG, DEP, ARR,
    sum (First_Class)over(partition by DEP)  First_Class1,
    sum (Economy)over(partition by DEP)  Economy1,
    sum (First_Class)over(partition by ARR)  First_Class2,
    sum (Economy)over(partition by ARR)  Economy2
    from #PAX
    )
    select DEP,max(ARR) ARR ,
    case when max(First_Class1)>max(First_Class2) then max(First_Class1) else max(First_Class2) end   First_Class,
    case when max(Economy1)>max(Economy2) then max(Economy1) else max(Economy2) end   Economy
    from cte
    group by DEP
    /*
    DEP  ARR  First_Class Economy
    ---- ---- ----------- -----------
    JFK  DXB  24          171
    STL  JFK  24          187
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 29, 2019 5:50 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 2, 2019 9:10 AM
  • Thanks Rachel! That helped. :)

    Known is a DROP, Unknown is an OCEAN.

    Tuesday, December 3, 2019 3:40 PM