Norm Norm - 6 months ago 19
SQL Question

Count of one of the columns

Let's say I have the dataset that looks like:

col1 col2 col3
a 2 20
a 3 12
a 4 34
b 2 44
c 3 23
c 5 13


....

What I want is a count of col1.

Output:

col1 col2 col3 count
a 2 20 3
a 3 12 3
a 4 34 3
b 2 44 1
c 3 23 2
c 5 13 2


.......

I know I can do by:

with cte as (
select col1, count(*) count
from tab1)
select a.col1,a.col2,a.col3,cte.count
from tab1
join cte on a.col1=cte.col1


But is there any other I can do that without cross apply or cte?

Also, assuming there are more than 3 letters in col1, so I couldn't use sum function either:

SUM(CASE WHEN ItemID = 'a' THEN 1 ELSE 0 END) AS count_a

Answer

If you're using SQL Server 2008+, you can use COUNT() OVER():

SELECT *,
    COUNT(*) OVER(PARTITION BY col1)
FROM tab1

ONLINE DEMO

Comments