ON AGGREGATION

ON AGGREGATION

I FOUND THIS EXERCISE ONLINE BY MYSELF. WOULD LIKE TO PRACTICE A
LITTLE BIT MYSELF.


Database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)

Company table has ID and name of the company, which transports
passengers. Trip table has information about trips: trip number,
company ID, plane type, departure city, arrival city, departure time,
and arrival time. The passenger table has passenger's ID and
passenger's name. Pass_in_trip table has information about the
flights: trip number, departure date (day), passenger's ID and his
place during the flight. We should note that,
- Any trip is being accomplished every day; duration of a flight is
less than a calendar-day (24 hours);
- Time and date are considered comparatively one time zone;
- The departure time and the arrival time are given to within a
minute;
- There can be the passengers bearing the same names (for example,
Bruce Willis);
- The place during the flight is a number followed by a letter; the
number defines the row number, the letter (a - d)  the place in the
row (from the left to the right) in the alphabetical order;
- Relationships and restrictions are shown in the data schema.

Exercise: 33
Find the dates where number of trips from town Rostov was the maximum.
Result set: number of trips, date



MY CODE IS THIS:
select count(*), date from Pass_in_trip
where trip_no in (select trip_no from Trip where town_from = 'Rostov')
group by date
having count(*) >= any
(select count(*) from Pass_in_trip

where trip_no in (select trip_no from Trip where town_from = 'Rostov')

group by date)

MY RESULT IS THIS:                                   BUT THE CORRECT
RESULT IS THIS:

Qty                   date
Qty                  date
1         2003-04-05 00:00:00.000                    1
2003-04-01 00:00:00.000
1         2003-04-13 00:00:00.000                    1
2003-04-05 00:00:00.000
1         2003-04-29 00:00:00.000                    1
2003-04-08 00:00:00.000
2         2003-04-08 00:00:00.000                    1
2003-04-13 00:00:00.000
2         2003-04-14 00:00:00.000                    1
2003-04-14 00:00:00.000
3         2003-04-01 00:00:00.000                    1
2003-04-29 00:00:00.000



DON'T KNOW WHY???
THANKS GUY!

date: Tue, 19 Aug 2008 16:12:32 -0700 (PDT) author: Yue

Re: ON AGGREGATION

> Database schema consists of 4 tables:
> Company(ID_comp, name)
> Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
> Passenger(ID_psg, name)
> Pass_in_trip(trip_no, date, ID_psg, place)

If you're counting Trips to determine day with the maximum number of
trips from Rostov , then why are you even including the Pass_in_Trip
table in your SQL?

Couldn't you determine this just by looking at the Trip table?

-Eric Isaacs

date: Tue, 19 Aug 2008 17:43:21 -0700 (PDT) author: Eric Isaacs

Re: ON AGGREGATION

HI, ERIC.
THANKS FOR REPLYING. SINCE ONLY THE PASS_IN_TRIP TABLE HAS THE "DATE"
COLUMN. AND I TESTED IT, EITHER TIME_OUT OR TIME_IN COLUMN  DOESN'T
HAVE THE RIGHT ENTRIES COMPARED TO THE SOLUTION.

AND ONLY THE "DATE" COLUMN FROM THE PASS_IN_TRIP HAS THEM.
I'VE GOT THE DATES RIGHT BUT THE COUNT # ISN'T .


THANKS.

date: Wed, 20 Aug 2008 17:38:56 -0700 (PDT) author: Yue

Re: ON AGGREGATION

Something like this might work, but it's obviously untested, so it
might still need some work:

SELECT
    TripCount,
    Date
FROM
    (SELECT
        COUNT(*) AS TripCount,
        TripDate.Date
     FROM
        (SELECT DISTINCT
            pt.trip_no,
            pt.date
         FROM
            Pass_In_Trip pt
            INNER JOIN Trip t ON pt.Trip_no = t.Trip_no
         WHERE
            t.Town_From = 'Rostov'
         GROUP BY
            Date) AS TripDate
     GROUP BY
        TripDate.Date) AS TripDateCount
WHERE
    TripCount = ANY ( SELECT MAX (TripCount) FROM TripDateCount )


-Eric Isaacs

date: Wed, 20 Aug 2008 23:27:00 -0700 (PDT) author: Eric Isaacs