list most frequent occurences in a table

[antonyx]

my database is set up like this:

2 TABLES

table 1 - passenger table
passengerID - PK
passengerNAME

table 2 - bookings table
bookingID - PK
passengerID - FK

i want a list of passenger ids from the bookings table ordered by the most no. of occurences in the bookings table. i would also only want each id to be displayed once in the list..

eg.. if passenger id 35 appears 500 times, passenger 21 appears 20 times, and passenger 87 appears 2,500 times the list should read..

87
35
21

what criteria can i put in my query to acheive this?

 

[gemma-the-husky]

you need a totals query

put both tables in a query grid, join them on passenger, and do a totals, group by passenger id, with count of bookingid (or anything really), sorted descending if that helps

use view/totals (or click the sigma character in the toolbar) (the unusual E shape)

 

[redneckgeek]

I don't think you even need the passenger table - unless you want the name.

SELECT PASSENGERID
FROM BOOKINGS
GROUP BY PASSENGERID
ORDER BY COUNT(BOOKINGID) DESC

 

[llkhoutx]

SELECT Table1.passengerID
FROM Table1 LEFT JOIN Table2 ON Table1.passengerID = Table2.passengerID
GROUP BY Table1.passengerID
ORDER BY Count(Table2.passengerID) DESC;

 

[antonyx]

hi that is great, i used redneckgeek's method and it is working (i dont need the passenger name and i havent included the passenger table in the query).

here is my sql at the moment.

SELECT fkPassengerID
FROM tblJob
GROUP BY fkPassengerID
ORDER BY COUNT(JobRef) DESC

the query produces a single column showing the unique passenger ids ordered by most frequent (great!)

is there a way i can add another column to this query which will show how many times the id appears??

 

[redneckgeek]

I was wondering why you weren't looking for that in the original question.

SELECT fkPassengerID, COUNT(JobRef) as cnt
FROM tblJob
GROUP BY fkPassengerID
ORDER BY COUNT(JobRef) DESC

 

[antonyx]

great, thanks a lot

 

[gemma-the-husky]

are you doing this directly in SQL?

its easier to use the design grid to develop most things