The right way to use group by

Databases
1

Hi,
can anyone explain me what’s the right way to use the group by clause ?

for instance


SELECT A.domain_id, U.birthday AS field, DATE( access_start_datetime ) AS DATE, COUNT( A.id ) AS value
FROM stats_access A
JOIN users U ON U.id = A.fb_id
GROUP BY DATE( A.access_start_datetime ) , U.birthday, A.domain_id

with this query I get the number of the users group by access_start_datetime,birthday and domain_id
Is it right ?

2

yeah, that looks fine

why, don’t you like the results it produces?

3

It works fine but I’d a doubt.
All in all I’d like to know what’s the
rules to apply group by
from http://www.tizag.com/mysqlTutorial/mysqlgroupby.php
Some technical rules of GROUP BY:
The column that you GROUP BY must also be in your SELECT statement.
Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the type column and the aggregate function was applied to the price column.
Is it enough ?

4

“The column that you GROUP BY must also be in your SELECT statement” is correct, except i would say the SELECT clause, not SELECT statement

5

Ok thanks, for instance


SELECT type, COUNT(name) FROM products GROUP BY type

in this case GROUP BY is mandatory or it’s enough


SELECT type, COUNT(name) FROM products
6

it’s mandatory

it’s kind of the reverse of “the column that you GROUP BY must also be in your SELECT clause”

if you have a non-aggregate column in your SELECT clause, it must be in the GROUP BY