gedq gedq - 5 months ago 9
SQL Question

multiple fields in a group by statement

After much hunting I'm forced to ask: All I want to do is add a field to a group statement. I have a simple little query:

SELECT product_id, count(*)
FROM uut_result
WHERE start_date_time = '2016-06-16 00:00:00'
GROUP BY product_id
ORDER BY product_id ASC;


So, for example, I want:

SELECT product_id, reception_date, count(*)
from uut_result
group by product_id


Which would give us:

KL2483 2016-07-14 48


that's all it is, and all I want to do is pull in one extra field in the select statement. But MySQL won't let me have more than two fields. Is there a simple way to get around that?

Answer

You can pull in another field by using an aggregation function:

SELECT product_id, MAX(othercol), count(*) 
FROM uut_result 
WHERE start_date_time = '2016-06-16 00:00:00' 
GROUP BY product_id 
ORDER BY product_id ASC;

Your server must have the mode ONLY_FULL_GROUP_BY set (perhaps via the ANSI mode). Otherwise, MySQL would allow additional columns in the SELECT.