Count e groubBy

USE tempdb;

CREATE TABLE dbo.foo(
c1 int NOT NULL,
c2 varchar(10) NOT NULL
);

INSERT dbo.foo VALUES
      (1, 'pippo')
    , (2, 'pluto')
    , (3, 'alex')
    , (4, 'pluto')
    , (5, 'pippo');

SELECT MIN(c1) AS id, c2 AS nome, COUNT(*) AS totaleNomi
FROM dbo.foo
GROUP BY c2
ORDER BY id;

/* Output:

id          nome       totaleNomi
----------- ---------- -----------
1           pippo      2
2           pluto      2
3           alex       1

(3 row(s) affected)

*/

DROP TABLE dbo.foo;