CODEWITHSUNDEEP
sqldatabase-designsqlite
Mike
Mike

Reputation: 21

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

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;

Upvotes: 0

Views: 10316

Answers (3)

Void Ray
Void Ray

Reputation: 10219

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

Upvotes: 1

SPFiredrake
SPFiredrake

Reputation: 3892

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.

Upvotes: 1

juergen d
juergen d

Reputation: 204854

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

Upvotes: 3

Related Questions