I am currently running a query like the following:
SELECT a.ID, a.ContactID, a.Code,
FROM tableA a
JOIN (SELECT ContactID, Code
FROM tableA
WHERE ContactID IS NOT NULL
GROUP BY Code, ContactID
HAVING COUNT(Code) > 1) b
ON (a.Code = b.Code AND a.ContactID = b.ContactID)
WHERE a.ContactID IS NOT NULL
ORDER BY a.Code
This returns data that looks like the folloing:
table : a
+-------+-----------+-----------+
| ID | ContactID | Code |
+-------+-----------+-----------+
| 1 | 111 | abcd2 |
| 2 | 111 | abcd2 |
| 3 | 222 | abcd1 |
| 4 | 222 | abcd1 |
| 5 | 222 | abcd1 |
| 6 | 222 | abcd1 |
+-------+-----------+-----------+
So as you can see I get ContactID's that have more then one of the same Code.
The problem with this is, is that I don't want all this output (real table is much larger). I want a COUNT to go along side the Code column and just show one row for each iteration of Code. Like the following:
+-------+-----------+-----------+------+
| ID | ContactID | Code |COUNT |
+-------+-----------+-----------+------+
| 1 | 111 | abcd2 | 2 |
| 3 | 222 | abcd1 | 4 |
+-------+-----------+-----------+------+
Any help on this would be great and I hope I have explained my problem well enough. If not please ask for more information and if this has been answered before please point in that direction.
Thanks.
Your solution and other answers are way to complicated, you don't need the self join when you're simply aggregating with HAVING Count(x) > 1:
SELECT MIN(ID), ContactID, Code, COUNT(Code) AS [COUNT]
FROM tableA
WHERE ContactID IS NOT NULL
GROUP BY Code, ContactID
HAVING COUNT(Code) > 1
Full solution:
CREATE TABLE TableA
([ID] int, [ContactID] int, [Code] varchar(5))
;
INSERT INTO TableA
([ID], [ContactID], [Code])
VALUES
(1, 111, 'abcd2'),
(2, 111, 'abcd2'),
(3, 222, 'abcd1'),
(4, 222, 'abcd1'),
(5, 222, 'abcd1'),
(6, 222, 'abcd1')
;
Query 1:
SELECT min(id), ContactID, Code, count(Code) as [COUNT]
FROM tableA
WHERE ContactID IS NOT NULL
GROUP BY Code, ContactID
HAVING COUNT(Code) > 1
| | ContactID | Code | |
|---|-----------|-------|---|
| 1 | 111 | abcd2 | 2 |
| 3 | 222 | abcd1 | 4 |
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments