CODEWITHSUNDEEP
sqlsql-serverselectgroup-bycase
user2571510
user2571510

Reputation: 11377

SQL Server: compare two columns in Select and count matches

I would like to do the following within a Select:

So far I have the following but don't know how to get the percentage and how to group properly in this case. Can someone here tell me how I have to adjust this to get what I need ?

BEGIN
    SET NOCOUNT ON;
    SELECT      (CASE WHEN A.col3 = A.col2 THEN '1' ELSE '0' END) AS match,
                (
                    SELECT      B.col1,
                                B.col2,
                                B.col3
                    FROM        LogTable B
                    WHERE       B.Category LIKE '2014-04%'
                    FOR XML     PATH(''), ELEMENTS, TYPE
                )
    FROM        LogTable A
    WHERE       A.Category LIKE '2014-04%'
    GROUP BY    CASE WHEN A.col3 = A.col2 THEN '1' ELSE '0' END
    FOR XML PATH('comparison'), ELEMENTS, TYPE, ROOT('ranks')

END

Many thanks for any help with this, Tim.

Upvotes: 1

Views: 5383

Answers (1)

Frazz
Frazz

Reputation: 3043

It' a bit different, but I would try something like this:

SELECT a.col1, a.total_count, b.match_count,
  (100*b.match_count/a.total_count) AS match_percentage
FROM (
  SELECT col1, COUNT(*) AS total_count
  FROM LogTable
  WHERE Category LIKE '2014-04%'
  GROUP BY col1
) a
JOIN (
  SELECT col1, COUNT(*) AS match_count
  FROM LogTable
  WHERE Category LIKE '2014-04%' AND col2=col3
  GROUP BY col1
) b ON a.col1=b.col1

As an alternative... this should give the same result. Not sure which would be more efficient:

SELECT col1, total_count,
  (SELECT COUNT(*)
   FROM LogTable
   WHERE Category LIKE '2014-04%' AND col1=a.col1 AND col2=col3
  ) AS match_count,
  (100*match_count/total_count) AS match_percentage
FROM (
  SELECT col1, COUNT(*) AS total_count
  FROM LogTable
  WHERE Category LIKE '2014-04%'
  GROUP BY col1
) a

But... beware... I'm not sure all engines are able to reference the subselect column match_count directly in the expression used to build the match_percentage column.

Upvotes: 2

Related Questions