How to count same record in a table

Try:

WITH CTE AS (
SELECT Color, COUNT(*) OVER (PARTITION BY Color) AS Freq, 
ROW_NUMBER() OVER (PARTITION BY Color ORDER BY (SELECT 1) ) AS RN
FROM Production.Product WHERE Color is not null)
SELECT Color, Freq FROM CTE WHERE RN=1
AND Freq > 1 ORDER BY Color;
/*
Black   93
Blue    26
Multi   8
Red     38
Silver  43
Silver/Black    7
White   4
Yellow  36
*/

---

---

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design

New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014