Get count of common field among the same records, SQL Server?-CodePudding

Assume we have the following records:

Id	Column A	Column B	Column C	Common
1	Value1	Value2	Value3	12
2	Value1	Value2	Value3	13
3	Value1	Value2	Value3	08
4	Value1	Value2	Value3	10
5	Value4	Value5	Value6	18
6	Value4	Value5	Value6	22
7	Value4	Value5	Value6	37

How can we get the following result

Id	Column A	Column B	Column C	CommonCount
1	Value1	Value2	Value3	4
5	Value4	Value5	Value6	3

I wrote this query:

SELECT 
    jr.*,
    CommonCount = (SELECT COUNT(1)
                   FROM la.JudicialReference AS jr1
                   WHERE ((jr.ColumnA = jr1.ColumnA) 
                          OR ISNULL(jr.ColumnA, jr1.ColumnA) IS NULL)
                     AND ((jr.ColumnB = jr1.ColumnB) 
                          OR ISNULL(jr.ColumnB, jr1.ColumnB) IS NULL) 
                     AND ((jr.ColumnC = jr1.ColumnC) 
                          OR ISNULL(jr.ColumnC, jr1.ColumnC) IS NULL))
FROM 
    la.JudicialReference AS jr

But it doesn't get me the first Id and I'm looking for a better query to save IO

CodePudding user response：

Simply

SELECT MIN(ID), ColumnA, ColumnB, ColumnC, COUNT(*)
FROM la.JudicialReference
GROUP BY ColumnA, ColumnB, ColumnC

Optionally with HAVING COUNT(*) > 1