count(*)

Hej!

Jag har följande kod:

select [Name], count(*) As Antal from tblUsers group by [Name] having count(*) > 1 order by [Name]

Fungerar fint, men hur får jag med [Id] Också...

Nu visas:

Name         Antal
-------------------
Namn 1       2
Namn 2       3
Namn 3       4

Jag vill visa:

Id        Name         Antal
-----------------------------
121657    Namn 1       2
687117    Namn 2       3
618160    Namn 3       4

Vad skanas i min kod?

Mvh
Henrik

select [b][id],[/b][Name], count(*) As Antal from tblUsers group by [Name] having count(*) > 1 order by [Name]

?

Jag har provat det... men då blir det fel..

Msg 8120, Level 16, State 1, Line 3
Column 'tblUsers.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Då provade jag att lägga till i GROUP BY enligt:

select [id],[Name], count(*) As Antal from tblUsers group by [Id],[Name] having count(*) > 1 order by [Name]

Men, då blirt det inga träffar alls... :(

Mvh
Henrik

select [id],[Name], count(*) As Antal from tblUsers group by [Name] having count(*) > 1 order by [Name], [id]

Tror jag.

select [Name], count(*) As Antal,
 ( select top 1 id from tblusers where name = u.name ) as id
 from tblUsers u group by [Name] having count(*) > 1 order by [Name]