CODEWITHSUNDEEP
sql
Gregism
Gregism

Reputation: 392

Using Group By, Count, and Having correctly

I have a list of categories and items, and I want to return only the categories that have one type of Item. For instance:

Stuff table:

Cat 1 | Item 1
Cat 1 | Item 1
Cat 1 | Item 2
Cat 1 | Item 2
Cat 1 | Item 3
Cat 1 | Item 3
Cat 1 | Item 3
Cat 2 | Item 1
Cat 2 | Item 1

I would like to return 

Cat 2  | Item 1

I tried:

SELECT category, item
FROM stuff
GROUP BY category, item
HAVING Count(Distinct item) = 1

But it's not working. I returns:

Cat 1 | Item 1
Cat 1 | Item 2
Cat 1 | Item 3
Cat 2 | Item 1

Upvotes: 0

Views: 52

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220952

You should remove item from your GROUP BY clause and run this instead:

SELECT category, MAX(item)
FROM stuff
GROUP BY category
HAVING COUNT(DISTINCT item) = 1

Example SQLFiddle.

Otherwise, each group returned from the GROUP BY clause will naturally have exactly one distinct item.

Upvotes: 1

Avt
Avt

Reputation: 17043

SELECT category, MAX(item)
FROM Table1
GROUP BY category
HAVING Count(Distinct item) = 1

Upvotes: 2

Daniel Sparing
Daniel Sparing

Reputation: 2173

Possibly your database does not allow you to have a variable in the select that is neither in the group by nor aggregated.

Taking advantage that there is only one item, you could try:

SELECT category, MIN(item) AS item
FROM stuff
GROUP BY category
HAVING Count(Distinct item) = 1

Upvotes: 0

Related Questions