Search code examples
sqlsql-servert-sqljoinwindow-functions

Count() how many times a name shows up in a table with the rest of info

I have read in various websites about the count() function but I still cannot make this work.

I made a small table with (id, name, last name, age) and I need to retrieve all columns plus a new one. In this new column I want to display how many times a name shows up or repeats itself in the table.

I have made test and can retrieve but only COLUMN NAME with the count column, but I haven't been able to retrieve all data from the table.

Currently I have this

   select a.n_showsup, p.*
   from [test1].[dbo].[person] p,
   (select count(*) n_showsup
    from [test1].[dbo].[person])a

This gives me all data on output but on the column n_showsup it gives me just the number of rows, now I know this is because I'm missing a GROUP BY but then when I write group by NAME it shows me a lot of records. This is an example of what I need:

test table

Solution

  • You can use window functions, if you RDBMS supports them:

    select t.*, count(*) over(partition by name) n_showsup
from mytable t

    Alternatively, you can join the table with an aggregation query that counts the number of occurences of each name:

    select t.*, x.n_showsup
from mytable t
inner join (select name, count(*) n_showsup from mytable group by name) x
    on x.name = t.name