SQL Having count logic

Question

i need help on HAVING COUNT , i have a result set of data below:

CREATE TABLE #tmpTest1 (Code VARCHAR(50), Name VARCHAR(100))

INSERT INTO [#tmpTest1]
(
    [Code],
    [Name]
)
SELECT '160215-039','ROBIN'
UNION ALL SELECT '160215-039','ROBIN'
UNION ALL SELECT '160215-046','SENGAROB'
UNION ALL SELECT '160215-046','BABYPANGET'
UNION ALL SELECT '160215-045','JONG'
UNION ALL SELECT '160215-045','JAPZ'
UNION ALL SELECT '160215-044','AGNES'
UNION ALL SELECT '160215-044','AGNES'
UNION ALL SELECT '160215-041','BABYTOT'
UNION ALL SELECT '160215-041','BABYTOT'
UNION ALL SELECT '160215-041','BABYTOT'

i want to show only the rows that have the same code but different name , so in this case my expected result is below since those are have the same code but different name:

160215-045  JAPZ
160215-045  JONG
160215-046  BABYPANGET
160215-046  SENGAROB

but when i try to group the two columns then use the having count, below is my query:

SELECT [Code], [Name] FROM [#tmpTest1] 
GROUP BY [Code], [Name] HAVING COUNT([Code]) > 1

It gives me wrong result below which have the rows that have the same code and name, it is the opposite of what i want.

160215-044  AGNES
160215-041  BABYTOT
160215-039  ROBIN

How can i get my expected output ?

Thanks in advance, any help would much appreciated.

What if you had `'160215-041','BABYTOT', '160215-041','BABYTOT', '160215-041','OTHER'` ? What would be returned in this case? — Tim Biegeleisen, Feb 16 '16 at 03:34
@TimBiegeleisen oh i see, that is a good question , i didn't consider that one.. if that is the case , i want also to show that 3 records, i mean if there is 1 different name for the codes , i want to show those records for me to know that there is one differs to others.. but if the code and name is all the same, i don't want those in the result... can you give me advise or help me on the right query ? Thanks in advance Tim. — japzdivino, Feb 16 '16 at 03:39

Tim Biegeleisen · Accepted Answer · 2016-02-16T04:03:56.983

2

I believe this query will give you the result you want, although your original question is a bit unclear.

SELECT t1.[Code], t1.[Name]
FROM [#tmpTest1] t1
INNER JOIN
(
    SELECT [Code]
    FROM [#tmpTest1] 
    GROUP BY [Code]
    HAVING COUNT(DISTINCT [Name]) > 1
) t2
    ON t1.[Code] = t2.[Code]

Follow the link below for a running demo:

SQLFiddle

edited Feb 16 '16 at 04:03

answered Feb 16 '16 at 03:38

Tim Biegeleisen

502,043
27
286
360

1

this one works for me ..i'll mark this as answer.. thank you so much.. big help to me :) .. regards – japzdivino Feb 16 '16 at 04:29

score 2 · Answer 2 · answered Feb 16 '16 at 03:56

2

If you want rows with the same code and name, then use window functions:

select t.*
from (select t.*, count(*) over (partition by code, name) as cnt
      from #temptest1 t
     ) t
where cnt >= 2;

answered Feb 16 '16 at 03:56

Gordon Linoff

1,242,037
58
646
786

ahm nope.. i want same code but different name.. anyways appreciate your answer. – japzdivino Feb 16 '16 at 04:27
one up vote because your answer also help me in other report requirement of my project.. regards :) – japzdivino Feb 16 '16 at 04:31

FuzzyTree · Answer 3 · 2016-02-16T04:05:11.690

1

From your comment

if there is 1 different name for the codes , i want to show those records for me to know that there is one differs to others..

This sounds like an exists query because you want to check if another row with the same code but different name exists.

select * from [#tmpTest1] t1
where exists (
  select 1 from [#tmpTest] t2
  where t2.code = t1.code
  and t2.name <> t1.name
)

edited Feb 16 '16 at 04:05

answered Feb 16 '16 at 03:47

FuzzyTree

32,014
3
54
85

if only the unique records, then i think the distinct might have sufficed. – Roy Feb 16 '16 at 03:50

SQL Having count logic

3 Answers3