CODEWITHSUNDEEP
sql
djlumley
djlumley

Reputation: 2967

Joining results of two different aggregate functions on the same table

I'm trying to combine two queries in order to determine which aircraft will be at a particular point (point ABCD) just before a particular time (2011-09-19 04:00:00.000). So far, I generate the last time an aircraft arrived at this point, and the last time an aircraft departed the point. My current idea is that if the last time it arrived is greater then the last time it departed the aircraft is still at point ABCD just before the specified time.

The first query finds the last time an aircraft arrived at a certain point:

select aircraft_id, MAX(arrival_datetime) as last_arrival
from flight_schedule
where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD'
group by aircraft_id

and the second query finds the last time an aircraft has left this point:

select aircraft_id, MAX(departure_datetime) as last_departure
from flight_schedule
where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD'
group by aircraft_id

Both of these queries generate the appropriate responses. I realise that in order to compare the last_departure to the last_arrival fields I need to join the tables somehow.

I'm not an SQL whiz, and in the past any table joins I've done have been between two completely different tables and haven't involved any aggregate functions so my normal subqueries and structure hasn't worked.

Upvotes: 8

Views: 15402

Answers (4)

Erin Houlroyd
Erin Houlroyd

Reputation: 1

Thank you so much. I have worked 10 hours spanning two days to get this to work.

In summary, a primary query wraps around any number of sub queries and by naming the sub queries with aliases, you can push data from the sub queries to the main query:

SELECT N.Name_CO_ID, N.Name_Company, N.District_Count, D.DistrictName, D.District_Response
FROM ( 
SELECT Name_CO_ID, Name_Company, COUNT(dbo.SV_Name.Name_CO_ID) AS 'District_Count'
FROM SV_Name 
JOIN dbo.SV_Activity ON dbo.SV_Name.Name_ID = dbo.SV_Activity.Activity_ID
JOIN dbo.SV_Product ON dbo.SV_Activity.Activity_PRODUCT_CODE = dbo.SV_Product.Product_PRODUCT_CODE
AND (dbo.SV_Activity.Activity_ACTIVITY_TYPE = 'COMMITTEE') 
AND (dbo.SV_Product.Product_GROUP_3 = 'DIST') 
AND (dbo.SV_Activity.Activity_THRU_DATE > GETDATE()) 
AND (dbo.SV_Name.Name_CO_MEMBER_TYPE = 'LSD')
GROUP BY Name_CO_ID, Name_Company) N
FULL OUTER JOIN (SELECT DistrictName, COUNT(DistrictName)AS 'District_Response'
FROM ODS_Landing
WHERE (StartDate > @StartDate1) AND (StartDate < @StartDate2)
GROUP BY DistrictName) D ON N.Name_COMPANY = D.DistrictName
WHERE District_Response > 0
ORDER BY Name_Company

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

The easiest solution would be to

  • turn each statement into a subquery
  • join the results together

SQL Statement

select la.aircraft_id, la.last_arrival, ld.last_departure
from  (
    select aircraft_id, MAX(arrival_datetime) as last_arrival
    from flight_schedule
    where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD'
    group by aircraft_id
  ) la
  full outer join (
    select aircraft_id, MAX(departure_datetime) as last_departure
    from flight_schedule
    where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD'
    group by aircraft_id
  ) ld on ld.aircraft_id = la.aircraft_id

Note that I've used a full outer join. Most likely an inner join would suffice. The full outer join is only needed if there's ever an arival_datetime without a departure_datetime or vice versa (wich is unlikely to happen).

Upvotes: 8

Andriy M
Andriy M

Reputation: 77657

The following solution uses standard SQL and should work in most, if not all, the major RDBMSes:

SELECT
  aircraft_id,
  CASE
    WHEN MAX(CASE arrival_point   WHEN 'ABCD' THEN arrival_datetime   END) >
         MAX(CASE departure_point WHEN 'ABCD' THEN departure_datetime END)
    THEN 'At the point'
    ELSE 'Somwhere else'
  END AS is_located
FROM flight_schedule
WHERE arrival_datetime   < '2011-09-19 04:00:00.000' AND arrival_point   = 'ABCD'
   OR departure_datetime < '2011-09-19 04:00:00.000' AND departure_point = 'ABCD'
GROUP BY
  aircraft_id

If your particular RDBMS supports CTEs and ranking functions, you could also try a different approach:

WITH events AS (
  SELECT
    aircraft_id,
    arrival_datetime AS event_datetime,
    'At the point' AS is_located
  FROM flight_schedule
  WHERE arrival_datetime < '2011-09-19 04:00:00.000'
    AND arrival_point = 'ABCD'
  UNION ALL
  SELECT
    aircraft_id,
    departure_datetime AS event_datetime,
    'Somewhere else' AS is_located
  FROM flight_schedule
  WHERE departure_datetime < '2011-09-19 04:00:00.000'
    AND departure_point = 'ABCD'
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY aircraft_id
      ORDER BY event_datetime DESC
    ) AS event_rank
  FROM events
)
SELECT
  aircraft_id,
  is_located
FROM ranked
WHERE event_rank = 1

Upvotes: 1

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

Try like this way :

select dpt.aircraft_id, last_arrival, last_departure
from
(
     select aircraft_id, MAX(arrival_datetime) as last_arrival 
     from flight_schedule 
     where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD' 
     group by aircraft_id 
) dpt
inner join 
    (
      select aircraft_id, MAX(departure_datetime) as last_departure 
      from flight_schedule 
      where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD' 
      group by aircraft_id 
    ) arr on dpt.aircraft_id = arr.aircraft_id

Upvotes: 3

Related Questions