CODEWITHSUNDEEP
sql-serverdistinct
user18209625
user18209625

Reputation: 189

SQL Server - Distinct

This is my table:

enter image description here

I want to know which names exist more than once with the source "comp" and the source "manual".

So in this case I want the output to be: host3 zyx (name and group) because the name host3 exists more than once and it has the source manual and the source comp.

I've tried this (SQL Server):

SELECT name, group 
FROM table
GROUP BY name
HAVING (COUNT(name) > 1) and ????

Upvotes: 0

Views: 116

Answers (3)

anon
anon

Reputation:

Another way to think about it is to calculate the counts inside a CTE and then filter:

; -- see sqlblog.org/cte
WITH cte AS
(
  SELECT name, 
         [group],
         SourceCount = COUNT(DISTINCT source)
  FROM dbo.tablename
  WHERE source IN ('comp', 'manual')
  GROUP BY name, [group]
)
SELECT name, [group]
  FROM cte 
  WHERE SourceCount = 2;

If you think CTEs are icky, or don't like that I write them defensively, you can also use a subquery:

SELECT name, [group] FROM
(
  SELECT name, [group],
         SourceCount = COUNT(DISTINCT source)
  FROM dbo.tablename
  WHERE source IN ('comp', 'manual')
  GROUP BY name, [group]
) AS q WHERE SourceCount = 2;

And again, the point is to provide another way to think about it, especially for new learners, not to use the fewest number of characters possible, or to appease people who can only think about queries in one way and that theirs is the only "good" or "right" way to solve a problem or teach others.

These two and the accepted answer all have identical performance, down to seven decimal places of subtree cost. Just because you don't like the look of my Ford Taurus doesn't mean it's a bad way for me to get downtown (or an unacceptable way to give someone a ride). I blogged about this here.

Upvotes: 2

Turo
Turo

Reputation: 4914

As I understand you want something like

SELECT name, max([group]) -- or STRING_AGG([group],',') 
FROM table
WHERE source in ('comp','manual')
GROUP BY name
HAVING COUNT(DISTINCT source) > 1

or you have to group by (in most sql dialects) group, too

SELECT name, [group]  
FROM table
WHERE source in ('comp','manual')
GROUP BY name, [group]
HAVING COUNT(DISTINCT source) > 1

Upvotes: 3

D-Shih
D-Shih

Reputation: 46219

I understand correctly, you can try to use condition aggregate function in HAVING COUNT with condition

We can use condition for WHERE to improve our performance if source creates a correct index.

SELECT name,[group]
FROM [table]
WHERE source in ('comp','manual')
GROUP BY name,[group]
HAVING COUNT(DISTINCT CASE WHEN source = 'manual' THEN source END) = 1
AND COUNT(DISTINCT CASE WHEN source = 'comp' THEN source END) = 1

Upvotes: 2

Related Questions