[SOLVED] How to group by 1 column with select *

How to group by 1 column with select *

i'm currently struggling with an SQL task.

I have an select Statement like this

SELECT * FROM data dt LEFT JOIN payment pm ON pm.month = dt.month AND pm.persID = dt.persID

Two Colums of data table are relevant for the condition (data.department, pm.payout).

Now i need all Information grouped by department where payout = 0.

I tried first to simply group by department

SELECT * FROM data dt LEFT JOIN payment pm ON pm.month = dt.month AND pm.persID = dt.persID GROUP BY dt.department

But this error occurs: Selected non-aggregate values must be part of the associated group.

Then i tried it with first checking if the payout is 0.

SELECT * FROM data dt LEFT JOIN payment pm ON pm.month = dt.month AND pm.persID = dt.persID WHERE pm.payout = 0

Solution

You need to mention column name with SELECT to use GROUP BY. i.e.

SELECT dt.department FROM data dt
JOIN payment pm ON pm.month = dt.month AND pm.persID = dt.persID
WHERE pm.payout = 0
GROUP BY dt.department

The above query will result only grouped departments. Since you want all the columns you need to look at this question. Need to return all columns from a table when using GROUP BY