SQL Server - Distinct-CodePudding

This is my table:

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 ????

CodePudding user response：

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;

Example db<>fiddle

CodePudding user response：

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

CodePudding user response：

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