Column must appear in the GROUP BY caluse in PostgreSQL


Error messsage:

ERROR: column "table.column" must appear in the GROUP BY clause or be used in an aggregate function Position

For example:

SELECT 
    b.id AS staff_id
    s.name AS staff_name,
    s.daily_salary AS daily_salary,
    s.ot_salary AS ot_salary,
    COUNT(CASE WHEN b.is_attendance >= 1 THEN 1 END) AS total_daily_count,
    SUM(b.ot_count) AS total_ot_count
FROM 
    bake_attendances AS b
JOIN
    staffs AS s
ON
    b.staff_id = s.id
GROUP BY 
    b.staff_id

Fix #1

SELECT 
    b.id AS staff_id
    s.name AS staff_name,
    s.daily_salary AS daily_salary,
    s.ot_salary AS ot_salary,
    COUNT(CASE WHEN b.is_attendance >= 1 THEN 1 END) AS total_daily_count,
    SUM(b.ot_count) AS total_ot_count
FROM 
    bake_attendances AS b
JOIN
    staffs AS s
ON
    b.staff_id = s.id
GROUP BY 
    b.staff_id, 
    b.id, s.name, s.daily_salary, s.ot_salary

Fix #2

SELECT 
    min(b.id) AS staff_id
    min(s.name) AS staff_name,
    min(s.daily_salary) AS daily_salary,
    min(s.ot_salary) AS ot_salary,
    COUNT(CASE WHEN b.is_attendance >= 1 THEN 1 END) AS total_daily_count,
    SUM(b.ot_count) AS total_ot_count
FROM 
    bake_attendances AS b
JOIN
    staffs AS s
ON
    b.staff_id = s.id
GROUP BY 
    b.staff_id

Reference