How to group by 1 column with select *-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Sorry if my answer is not grammatically correct, English is not my native language.

You have to put the arguments in the proper order.

Link

I don't know what type of columns you have in the table, and what results you want to get. I mean, I don't know if you want to count() or sum() a column.

I have understood in your post that you only want to see the results in which 'payout=0'.

For example, With the information you provided, I would do the following:

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

Have I helped you?

If not, give me more information to see what we can do.