SQL query help

[bob786]

Hello everyone and hope you all are okay.

Bacially i have a table that is called chalet, and the database is about a booking system.

Now what i want to work out is, how do you find out which chalet is popular. I.E one, two or three.. bedroom chalet..

How would i find out which one is mostly booked.

Its been bugging me all day.

Any help would be much appreciated

 

[pbaldy]

Try a query along the lines of:

SELECT ChaletType, Count(*) AS HowMany
FROM TableName
GROUP BY ChaletType

Changing the names as appropriate.

 

[bob786]

Hmm it sounds good, but im just thinking in my head how it will work.

Cos the Chalet just shows the descriptions of the type.. and there is another table called party leader, and that table includes which chalet is chosen, and ditto with customer table too.

So do i need to have another table that just shows the combined results of the two tables or something as I'm not clear in how to achieve this really..

 

[pbaldy]

Well, I don't know enough about your structure to be any more specific. Can you post the db and the expected result?

 

[bob786]

Well, the expected result is to show which chalet is popular when party leader makes the booking and to result that as a Query, but not sure how. Just checked that the customer data doesnt need to select the chalet, its only the Party leader that chooses the chalet.

Hope this helps.

 

[pbaldy]

Not really (unless I'm having afternoon brain cramps). If you can at least give us some idea of what the data looks like (a screenshot or something if you can't post the db). The SQL I posted would work if the type was in the booking table, which I would typically expect.

 

[bob786]

Right okay, well below is the screen shot of the database and the Booking table, with Customer ID, this shows which chalet is selected.

Hope this problem can be resolved.

http://yfrog.com/1gdatabasepj

 

[DCrake]

I'm confused also. Is the party leader a customer? ie first named customer. Or are they a representative of the customer.
Also I could see that you are using lookup tables in your table. This is frowned upon here for very good reasons. You may need to take advice on changing this strategy.

 

[bob786]

Pbaldy, I tried the SQL that you had suggest, but it works to some extent. It just shows the maximum number of Chalet.

Now i have looked at my chalet table and in there also shows which booking is made for each of the chalets.

Im thinking would i need to have an outer join?

 

[bob786]

No party leader is not a customer.. basically the party leader can make a booking on behalf of the customer. i.e If i was to call up and say bla bla bla want to make a booking for this person, they would need my details.. I could be paying for you to go on the holiday.

 

[bob786]

Hmm I dont think I can change the lookup tables now as this is due in on Monday, so im limited with time.

 

[DCrake]

So to answer your initial question you want to know which party leader makes the most bookings and what types of chalets are they actually booking?

If this is the case then you simply need to group by party leader by chalet type and count for each. Then sort ascending on count of party leader then by chalet type.

 

[bob786]

Dcrake, the way you put it would be a better solution just to having show which party leader made which booking and to show the popularity of the chalet.

So how would i do that in SQL? Much appreciated

 

[DCrake]

You would do this in a query. Bring down the fields in question and click on the sigma icon. Your SQL would look something like this

SELECT PartyLeader, Count(1) AS Leaders, ChaletLtype, Count(1) AS Chalets
FROM Bookings
GROUP BY PartyLeader, ChaletType
ORDER BY PartyLeader, ChaletType Desc;

 

[bob786]

Oh okay thanks, just going to see if it works.

Do I need to have the party leader table, chalet and the booking table in one query?

 

[bob786]

Hmm i tried the above but not sure if it is working really. As you suggested, it works okay but it still shows the maximum number of records that i have in the table which is 76.

Don't know why it is not working. Want something like Chalet type 1= 6 (meaning how many customers have booked that. There are 9 chalets.

 

[DCrake]

Can you post a cut down version of the mdb to look at?

 

[bob786]

Erm not sure if i can post the cut down version of db. I may need to post the full database, but not sure if Admins allow me to post a full database.

 

[DCrake]

Are you talking your admins? Best to zip up first. 2MG file size limit.