CODEWITHSUNDEEP
sql-server
Infravision
Infravision

Reputation: 139

How to count numbers for each group?

Let's say there is a result set...I need to print it out like so:

ID  Count
1    5
1    5 
1    5
1    5
1    5
2    2
2    2
3    1

Thanks in advance.

Upvotes: 1

Views: 117

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Do you mean that your query:

SELECT ID, COUNT(*) AS "Count"
FROM tableX
GROUP BY ID ;

produces this:

ID  Count
1    5
2    2
3    1

but you want this?:

ID  Count
1    5
1    5 
1    5
1    5
1    5
2    2
2    2
3    1

Then, this query will do:

SELECT grp.ID, grp."Count"
FROM 
        tableX AS t
    JOIN
        ( SELECT ID, COUNT(*) AS "Count"
          FROM tableX
          GROUP BY ID 
        ) AS grp
      ON grp.ID = t.ID ;

It will work in almost all DBMS and in all versions of SQL-Server. For SQL-Server versions 2005 and newer (and also in Oracle and Postgres), the answer with the OVER clause looks more elegant and may be prefered. Test in your version which one is more efficient. I think that in 2012 version, queries with OVER clause are quite efficient.

Upvotes: 8

David Weinberg
David Weinberg

Reputation: 1091

You can use count() with OVER clause:

select a, count(*) over (partition by a) as [count]
from tableName ;

It's called window function. I recommend you study these.

Upvotes: 8

Related Questions