georged georged - 3 years ago 162
SQL Question

selecting values from a table and counting them but not include that value

I am trying to select a number of things from a table such as name, address, town and then show the number of times that town appears e. Mr A, the street, town, 3.
I want to produce something like

Personid Forename Surname Groupid count
1 John Bloggs 1 2
2 Josh Bloggs 1 2
3 Bert Bloggs 1 2
4 Owen Jones 2 1
5 Jack Rose 3 1


enter image description here

Answer Source

try working with this: http://sqlfiddle.com/#!6/591ea6/1 Not sure If I understand completely what you need

SELECT Personid
    ,Forename
    ,Surname
    ,z.Groupid
    ,c.count
FROM TableZ z
INNER JOIN (
    SELECT groupid
        ,count(groupid) AS [count]
    FROM TableZ
    GROUP BY groupid
    ) c ON c.Groupid = z.Groupid
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download