BruceyBandit BruceyBandit - 1 month ago 17
SQL Question

How to add a count/sum and group by in a CTE

Just a question on displaying a row on flight level and displaying a count on how many crew members on that flight.

Below is a screenshot of three records that all belong to the same flight (same flight number and departure date) and within that flight are three crew members (hence why we see three records).

enter image description here

I want to change the output so it will only display a single record at flight level and it will display two additional columns. One column (cabincrew) is the count of crew members that have the

'CREWTYPE' = 'F'
and the other column (cockpitcrew) is the count of crew members that have the `'CREWTYPE' = 'C'.

So the query result should look like:

Flight DepartureDate DepartureAirport CREWBASE CockpitCrew CabinCrew
LS361 2016-05-19 BFS BFS 0 3


Can I have a little help tweaking the below query please:

WITH CTE AS (
SELECT cd.*, c.*, l.Carrier, l.FlightNumber, l.Suffix, l.ScheduledDepartureDate, l.ScheduledDepartureAirport
FROM
(SELECT *, ROW_NUMBER() OVER(PARTITION BY LegKey ORDER BY UpdateID DESC) AS RowNumber FROM Data.Crew) c
INNER JOIN
Data.CrewDetail cd
ON c.UpdateID = cd.CrewUpdateID
AND cd.IsPassive = 0
AND RowNumber = 1
INNER JOIN
Data.Leg l
ON c.LegKey = l.LegKey
)
SELECT
sac.Airline + CAST(sac.FlightNumber AS VARCHAR) + sac.Suffix AS Flight
, sac.DepartureDate
, sac.DepartureAirport
, sac.CREWBASE
, sac.CREWTYPE
, sac.EMPNO
, sac.FIRSTNAME
, sac.LASTNAME
, sac.SEX
FROM
Staging.SabreAssignedCrew sac
LEFT JOIN CTE cte
ON sac.Airline + CAST(sac.FlightNumber AS VARCHAR) + sac.Suffix = cte.Carrier + CAST(cte.FlightNumber AS VARCHAR) + cte.Suffix
AND sac.DepartureDate = cte.ScheduledDepartureDate

Answer

PLEASE TRY THIS.

  SELECT    Flight,
            DepartureDate,
            DepartureAirport,  
            CREWBASE,
            SUM(CASE WHEN CREWTYPE = 'F' THEN 1 ELSE 0 END) AS CabinCrew ,
            SUM(CASE WHEN CREWTYPE = 'C' THEN 1 ELSE 0 END) AS CockpitCrew
      FROM  @Table
    GROUP BY Flight, DepartureDate, DepartureAirport, CREWBASE
Comments