mySQL COUNT query

Databases
1

Apologies in advance if this is a really dumb question but I searched all over for a simple solution and failed…
I have a really simple query to see what grade of beds are available (bedStatusID = 5) in a hotel which works great

SELECT type AS bedType, COUNT(bedID) AS curCount
FROM bed
    JOIN hotel on hotelID = bedHotelID
    LEFT JOIN type ON typeID = bedTypeID
WHERE bedStatusID = 5 AND bedHotelID = 40 GROUP BY bedTypeID;

bedType curCount
premium 181
deluxe 22
standard 12

My probelm is that when there are no statdard beds available I get this:

bedType curCount
premium 181
deluxe 22

And I want to get this:

bedType curCount
premium 181
deluxe 22
standard 0

I’ve tried Coalesce, SUM, IFNULL etc but I must be getting the systax wrong as nothing seems to work…
Is there a way this can be achieved in MySQL?

2

You mean GROUP BY type, don’t you? I mean, bedTypeID is not in the list of fields that should be returned in the result and when you use GROUP BY, normally you don’t use the alises but the real name of the filed

3

@molona, Thanks for the fast response but my GROUP BY has no effect on what I’m trying to achieve. With the JOIN on type I can GROUP BY either but thanks for the tip on the Alias? I’ve tried all variations (alias, no aliad, type, bedTypeID, typeName etc) and the result is the same… I still don’t get ‘standard 0’ when there are no standard beds available…

4

You may be right but as far as I know the fields that go on the GROUP BY are those that are on the SELECT area but don’t use aggregate function.

You may have to use FROM LEFT JOIN hotel on hotelid=bedHotelID LEFT JOIN type ON typeID = bedTypeID in your FROM

5

@molona - Thanks again for your input but I’m afraid its not really helping me toward an answer. I’m reluctant to code a PHP workaround if there is something that can be done inside mySQL…

6

here ya go…

SELECT type.typeID AS bedType , COUNT(bed.bedID) AS curCount FROM type LEFT OUTER JOIN bed ON bed.bedTypeID = type.typeID AND bed.bedStatusID = 5 AND bed.bedHotelID = 40 GROUP BY type.typeID;
notice you don’t actually need the hotel table for this query

also, please don’t stick the table name into the column name at the front – that’s a clumsy and overly verbose naming convention

when more than one table is in the FROM clause, you should use the table names to qualify the column names as shown above, instead of trying to distinguish them using that horrible naming convention

7

@r937… You played a blinder Sir. That works really well… And thanks for the additional advise too… heeded and leant… Thanks again

8

thanks!!

are you also a corrie fan like me?

9

@r937 … By Corrie you also mean ‘The Street’ I presume? Raised on the stuff…

10

yes… i’m in canada but i’ve been watching since, well, since eddie yeats

11

@r937 God bless ya Gov’… Haven’t seen it for a while now but Remember Eddie Yates… very well… A real character…With Stan and Ilda Ogden… My personal favourites… Watched it since the sixties…Never a dull moment…
on a side note r937 - As you are problably aware, I’m trying to learn MySQL and clearly struggling in places… Would it be acceptable to PM you with something in a hope for some guidance?

12

i would very much prefer to see your questions on this forum… obfuscate the table/column names if necessary…

13

@937 - Understood, I thought that might be the case… but as Eddie Yeats would say… Arh, come ed, no harm, no foul…

Though I’ll warn you in advance, some of my quetions may appear on the face of it quite purile…

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.