SELECT
BB
,COUNT(1) AS COUNT_NORMAL
,COUNT(1) OVER(PARTITION BY CC) AS COUNT_OVER
,COUNT(CASE WHEN BB = CC THEN 1 ELSE NULL END) AS COUNT_CASE
FROM TABLE
GROUP BY BB
SELECT
BB
,COUNT(1) AS COUNT_NORMAL
,COUNT(1) OVER(PARTITION BY CC) AS COUNT_OVER
,COUNT(CASE WHEN BB = CC THEN 1 ELSE NULL END) AS COUNT_CASE
FROM TABLE
GROUP BY BB