whisher — 2013-04-22T15:54:06-04:00 — #1
can anyone explain me what's the right way to use the group by clause ?
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 ?
r937 — 2013-04-22T18:32:08-04:00 — #2
yeah, that looks fine
why, don't you like the results it produces?
whisher — 2013-04-23T03:50:03-04:00 — #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
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 ?
r937 — 2013-04-23T04:10:53-04:00 — #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
whisher — 2013-04-23T04:19:37-04:00 — #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
r937 — 2013-04-23T05:57:41-04:00 — #6
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