SQL Query - Display Count & All ID's With Same Name

Question

I'm trying to display the amount of table entries with the same name and the unique ID's associated with each of those entries.

So I have a table like so...

Table Names
------------------------------
ID    Name
0    John
1    Mike
2    John
3    Mike
4    Adam
5    Mike

I would like the output to be something like:

Name | Count | IDs
---------------------
Mike     3     1,3,5
John     2     0,2
Adam     1     4

I have the following query which does this except display all the unique ID's:

select name, count(*) as ct from names group by name order by ct desc;

It makes a big differnce in the code which database backend you are using, the solutions for this are client-specific. — HLGEM, Aug 06 '12 at 17:49
yet another reason `write once run anywhere` SQL is a pipe dream! @Mike, you need to specify which database you need this for, there is no method that will work in all databases. — KM., Aug 06 '12 at 18:19

score 3 · Answer 1 · answered Aug 06 '12 at 17:46

3

select name,
       count(id) as ct, 
       group_concat(id) as IDs
from names
group by name
order by ct desc;

You can use GROUP_CONCAT for that

answered Aug 06 '12 at 17:46

juergen d

201,996
37
293
362

2

That will not work in SQL Server, but I think it will in mySQL – HLGEM Aug 06 '12 at 17:48
[`group_concat()` works for SQLite, too](http://www.sqlite.org/lang_aggfunc.html). – Erwin Brandstetter Aug 07 '12 at 08:02

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

Depending on version of MSSQL you are using (2005+), you can use the FOR XML PATH option.

SELECT 
    Name, 
    COUNT(*) AS ct, 
    STUFF((SELECT ',' + CAST(ID AS varchar(MAX)) 
           FROM names i 
           WHERE i.Name = n.Name FOR XML PATH(''))
        , 1, 1, '') as IDs
FROM names n
GROUP BY Name
ORDER BY ct DESC

Closest thing to group_concat you'll get on MSSQL unless you use the SQLCLR option (which I have no experience doing). The STUFF function takes care of the leading comma. Also, you don't want to alias the inner SELECT as it will wrap the element you're selecting in an XML element (alias of TD causes each element to return as <TD>value</TD>).

Given the input above, here's the result I get:

Name    ct  IDs
Mike    3   1,3,5
John    2   0,2
Adam    1   4

EDIT: DISCLAIMER

This technique will not work as intended for string fields that could possibly contain special characters (like ampersands &, less than <, greater than >, and any number of other formatting characters). As such, this technique is most beneficial for simple integer values, although can still be used for text if you are ABSOLUTELY SURE there are no special characters that would need to be escaped. As such, read the solution posted HERE to ensure these characters get properly escaped.

edited May 23 '17 at 12:15

Community

1
1

answered Aug 06 '12 at 18:06

SPFiredrake

3,852
18
26

your answer will not properly handle XML characters like: `<` or `&` or `>`. For the proper way to do XML concatenation in SQL Server 2005+ go here: http://stackoverflow.com/a/5031297/65223 – KM. Aug 06 '12 at 18:12
For simple ID's (which is what he's looking for) this is fine, although I agree you have to be careful with this technique. – SPFiredrake Aug 06 '12 at 18:13
@SPFiredrake, this answer will be a big fail for the poor guy that reaches this page in the future and tries to concatenate the special characters. The same will happen when the OP tries to reuse this method and they start getting `&` and `>` sprinkled within their result set. – KM. Aug 06 '12 at 18:15
Added a disclaimer. However, there are answers with a lot more traffic that would come up well before this answer would that include the fix, so I'm not too worried about it being a big bowl of fail. Honestly, I like to leave my solutions as simple as possible and let the copy/pasta army just trip over themselves when they realize it doesn't apply to every situation. Hell, even I trip myself up every once in a while with this technique. – SPFiredrake Aug 06 '12 at 18:45

Void Ray · Answer 3 · 2012-08-06T23:07:25.533

Here is another SQL Server method, using recursive CTE:

Link to SQLFiddle

; with MyCTE(name,ids, name_id, seq)
as(
    select name, CAST( '' AS VARCHAR(8000) ), -1, 0
    from Data
    group by name
    union all
    select d.name,  
            CAST( ids + CASE WHEN seq = 0 THEN '' ELSE ', ' END + cast(id as varchar) AS VARCHAR(8000) ),
            CAST( id AS int),
            seq + 1
    from MyCTE cte 
    join Data d
        on cte.name = d.name
    where d.id > cte.name_id
)
SELECT name, ids
      FROM ( SELECT name, ids,
                    RANK() OVER ( PARTITION BY name ORDER BY seq DESC )
               FROM MyCTE ) D ( name, ids, rank )
     WHERE rank = 1

SQL Query - Display Count & All ID's With Same Name

3 Answers3

EDIT: DISCLAIMER