MYSQL trying to return duplicate values

Question

I'm trying to get my sql query to return only duplicate values from a table.

SELECT unitname, staff_id
FROM Unit
GROUP BY unitname, staff_id
HAVING COUNT(staff_id) >1;

But it keeps returning 0 rows, I don't know what to do.

0 is possibly correct if staff id is the same but in different unitname. I would take the unitname out of the query to establish if there are duplicates on staff id. — P.Salmon
– P.Salmon, Commented Oct 15, 2020 at 7:47
Then there are no duplicates on (unitname, staffid). Please provide sample data and desired results, as tabular text, to clarify your question. — GMB
– GMB, Commented Oct 15, 2020 at 7:48

Gordon Linoff · Accepted Answer · 2020-10-15 11:40:47Z

1

If you want the units that have multiple staffs, then:

SELECT unitname, GROUP_CONCAT(staff_id)
FROM Unit
GROUP BY unitname
HAVING COUNT(staff_id) > 1;

answered Oct 15, 2020 at 11:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gotqn · Accepted Answer · 2020-11-05 06:25:11Z

0

Try this query:

SELECT unitname, staff_id FROM Unit HAVING COUNT(staff_id) >1 GROUP BY unitname;

gotqn

44k47 gold badges167 silver badges257 bronze badges

answered Oct 22, 2020 at 7:03

Purvi Barot

2692 silver badges9 bronze badges