1

I have a table like this:

c1|c2
a| 2
b| 1
a| 1
b| 3
a| 2

how count this to get result like this

col1| col2| number of occurrences
a| 1| 1
a| 2| 2
b| 3| 1

And after get max value like this:

col1col2
a|2
b|1

Adrian Adyms
  • 15
  • 6

2 Answers2

2

Count 1 is your friend, count * leads to a longer load time.

SELECT C1, C2, COUNT(1) AS numberofoccurrences
FROM TABLE 
GROUP BY C1, C2

Sorry for delay. To get Max use following;

; WITH CTE AS(
    SELECT c1, c2, COUNT(1) AS NoOfOccurence FROM TEST
    GROUP BY c1, c2
), BTE AS(
    SELECT c1, c2, ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY NoOfOccurence DESC) AS RN FROM CTE
)SELECT c1, c2 FROM BTE WHERE RN = 1
Esty
  • 1,882
  • 3
  • 17
  • 36
  • 2
    Your assertion that `count(1)` would be better than `count(*)` is incorrect; they are equivalent. See https://stackoverflow.com/questions/1221559/count-vs-count1 for a thorough discussion on the subject. – jpw Oct 06 '16 at 12:00
  • Thats work fine, thanks, I have last questions, how get from this max value, like this a|2|2 b|3|1 – Adrian Adyms Oct 06 '16 at 12:12
  • That's funny. `count(1)` would be *slower*, wouldn't the optimizer convert it to `count(*)` internally. `count(*)` = select rows. `count(1)` = select rows, generate a 1 in every row, check whether the 1 is not null, if it isn't count it. – Thorsten Kettner Oct 06 '16 at 12:18
0

Here's a complete solution which includes the final max value output. Also note that an index on c1 and c2 will drastically improve performance. 

-- Sample data
-- including an id for use as a tie-breaker for a unique pk/clustered index on c1 & c2
DECLARE @tbl TABLE 
  (id int identity not null, c1 char(1), c2 tinyint, primary key (c1,c2,id));
INSERT @tbl VALUES ('a', 2), ('b', 1), ('a', 1), ('b', 3), ('a', 2);

-- Solution
SELECT c1, MaxOccurs = MAX(c)
FROM
(
  SELECT c1, c2, c = COUNT(1)
  FROM @tbl
  GROUP BY c1, c2
) x
GROUP BY c1;
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18