CODEWITHSUNDEEP
mysqlsql
gedq
gedq

Reputation: 610

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?

Upvotes: 0

Views: 93

Answers (3)

gedq
gedq

Reputation: 610

The answer that worked best for me looks to have been deleted. Shame. For the record I can have the extra field I need as long as I group by it:

SELECT age, height, max(goal_average) FROM players 

GROUP BY age, height <-- it's this second grouping that allows the height field.

etc.

Helped me anyway, I just wish I could give credit where it's due.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

One way to add an additional column to your result set is to place your current GROUP BY into a subquery, and then join it back to the original table:

SELECT t1.product_id, t1.reception_date, t2.product_count
FROM uut_result t1
INNER JOIN
(
    SELECT product_id, COUNT(*) AS product_count
    FROM uut_result 
    WHERE start_date_time = '2016-06-16 00:00:00' 
    GROUP BY product_id 
) t2
    ON t1.product_id = t2.product_id

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 0

Related Questions