GROUP BY "questions".

[igorek]

okay here we go =D

How does group by work with count()

GROUP BY
Grouping
The GROUP BY keyword allows you to regroup the rows of a recordset according to the recordset column names into one single row.

// create 2 tables

create table room(id int(5) auto_increment, price int(2), bed int(1), number int(3), primary key(id));
create table bookings(id int(5) auto_increment, roomid int(2), clientid int(2), primary key(id));

// insert values

insert into room(price,bed,number) values(1,34,2,101),(2,56,3,102);
insert into bookings(id,roomid,clientid) values(23,2,1),(24,2,2);

// Now the interesting part. SELECT Statement

SELECT room.id as roomid,price,bed,number,booking.id as bookingid from room left join bookings on room.id=roomid;

results: 3 rows of data, bookingid for roomid =1 is NULL the other two for roomid 2 are 23,24.

// now select statement with count
SELECT room.id as roomid,price,bed,number,booking.id as bookingid, count(roomid) as bookcount from room left join bookings on room.id=roomid GROUP BY room.id;

results: book count for roomid 1 is 0 and bookcount for roomid 2 is 2

What I do not understand is why it gets the 0 since it counts the roomids and each row has roomid one has 2 other has 1 so it should be 1,2 instead its 0,2.

Now my conclusion came to this that when you use GROUP BY with function count(expr) that row from bookings table is missing a roomid when its joined with roomid even though they are together, we are not seeing that info.

ahh cant think.

[johanafm]

Wrap your code in code tags. Uppercase reserved words so it becomes easier to read your code, and making use of newlines and indenting is a good idea as well. At least if you want someone else to read your code. I didn't, but I can give you some generic information and create my own examples instead.

First off, do yourself a favor and do NOT utilize the MySQL extension (I prefer the word insanity in this case) to aggregate functions and grouping.
The standard states (for good reason), that every column must be part of either the group by clause or an aggregate function, if you group data.
Consider a person table with id, fname, lname, initials, where initials are first char of fname and lname (redundant data, but used to make a point).

SELECT id, fname
FROM person

obviously shows all persons

SELECT count(id), fname
FROM person
GROUP BY fname

Now, the output is grouped on first name, and for each such group, the ids are counted, which means the result shows how many people have the same fname.

SELECT count(id), fname, lname
FROM person
GROUP BY fname, lname

Same as above, but now both fname and lname must match.

On to MySQL's extension...

SELECT count(id), fname, lname, initials
FROM person
GROUP BY fname, lname

This code won't work with standard SQL, since initials is not part of the group by clause nor an aggregate function. However, initials is the same for all rows that are grouped together (obviously, since it's based on all the the things you group by), so this is "safe".

SELECT id, fname, lname
FROM person
GROUP BY fname, lname

This however, is not safe. Output will show ANY id AT ALL (still assuming you're using MySQL), from one of the rows that are grouped together. And you don't know which.

So start by dropping everything from your query which is neither in an aggregate function or the group by clause. Then inspect your values again. Feel free to post code and ask for more help, but you really should use code tags, line breaks etc. If your code isn't easily readable, there is low chance that anyone will read it.

[igorek]

// create 2 tables

CREATE TABLE room
(id int(5) auto_increment, price int(2), bed int(1), number int(3), primary key(id));
CREATE TABLE bookings
(id int(5) auto_increment, roomid int(2), clientid int(2), PRIMARY KEY(id));

// insert values

INSERT INTO room(price,bed,number) 
VALUES(1,34,2,101),(2,56,3,102);

INSERT INTO bookings(id,roomid,clientid) 
VALUES(23,2,1),(24,2,2);

// Now the interesting part. SELECT Statement

SELECT room.id AS roomid,price,bed,number,booking.id AS bookingid 
FROM room 
LEFT JOIN bookings ON room.id=roomid;

results: 3 rows of data, bookingid for roomid =1 is NULL the other two for roomid 2 are 23,24.

// now select statement with count
SELECT room.id AS roomid,price,bed,number,booking.id AS bookingid, 
count(roomid) AS bookcount 
FROM room left join bookings ON room.id=roomid 
GROUP BY room.id;

OK. Thank you for your advice I guess I do understand how GROUP BY works now the question comes from is where does the count() function counts the rows as 0 for the roomid where in fact there are roomids in every record.

Do you see what I mean? I just want to understand that, thought someone may give a quick and an easy anwser to my problem, which is understanding how does count() works with GROUP BY and then again with LEFT JOIN. I mean I probably should take the statement apart and see if the LEFT JOIN is the factor.

[igorek]

figured it out, the problem is that both tables have roomid's.

and in my statement i call room.id AS roomid.

but the count() function counts the roomid's in the booking table which is a column with that name and not an ALIAS 🙂)) hehe sorry just couldnt see where it came from before but yet its always so simple.