John August John August - 2 months ago 6
SQL Question

How to return unique rows having count() of multiple columns = 1 using group by?

So here is my situation:

____________________________________________
| idnumber | name | sectiongroup |
--------------------------------------------
| 123 | Joe | one |
| 123 | Barry | two |
| 1234 | Laura | one |
| 1234 | LauraCopyCat | one |
--------------------------------------------


I am trying to build a query which will return any unique (i.e. -
COUNT(idnumber) = 1
) id numbers in a given
sectiongroup
. So if you are in sectiongroup number one and no one else in your sectiongroup has the same ID number as you, then I want your idnumber. If someone in group two happens to have the same idnumer, that is okay, I still want your idnumber.

For example, Barry and Joe have the same id number but they are in separate sectiongroups, so I want to return their idnubers. However, Laura and LauraCopyCat have the SAME sectiongroup, so I do NOT want their idnumbers to be returned. So far I have the following:

SELECT idnumber
FROM namestable
GROUP BY idnumber, sectiongroup
HAVING(COUNT(idnumber) = 1)


Is there a way to add sectiongroup into the COUNT()=1 condition?

Answer

Just use COUNT(*) to avoid confusion. This will count the number of records in the particular group. Remember, a group consists of the unique combinations of values in the fields specified in your GROUP BY statement.

SELECT idnumber
FROM namestable 
GROUP BY idnumber, sectiongroup
HAVING COUNT(*) = 1

Note that this will result in duplicate idnumbers, if you have records that share an id but have different subgroups. To remove duplicate, just change SELECT to SELECT DISTINCT.

Tested here: http://sqlfiddle.com/#!9/b0a50c/3