CODEWITHSUNDEEP
sql-server
bhavinp
bhavinp

Reputation: 833

SQL Query to find the number of rows with a common field

In MS SQL Server 2008. How do I count the number of rows that have a certain field in common. Example, lets say I have the following table.

UserAccount_ID Attribute_ID
786            11
797            10
797            5
797            6
797            11
555            11
1003           10
1003           5
1003           6
1003           11
633            11
2036           11
2087           10
2087           5
2087           6
291            11

What query do I need to perform to get the following table. This will count the number of records with the same UserAccount_ID

UserAccount_ID Count
    786            1
    797            4
    555            1
    1003            4
    633            1
    2036            1
    2087            3
    291              1

Upvotes: 1

Views: 799

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

create table #YourTable (
    UserAccount_ID int,
    Attribute_ID int
)

insert into #YourTable
    (UserAccount_ID, Attribute_ID)
    select 786,11 union all
    select 797,10 union all
    select 797,5 union all
    select 797,6 union all
    select 797,11 union all
    select 555,11 union all
    select 1003,10 union all
    select 1003,5 union all
    select 1003,6 union all
    select 1003,11 union all
    select 633,11 union all
    select 2036,11 union all
    select 2087,10 union all
    select 2087,5 union all
    select 2087,6 union all
    select 291,11

select UserAccount_ID, count(*)
    from #YourTable
    group by UserAccount_ID

drop table #YourTable

Upvotes: 4

gbn
gbn

Reputation: 432431

It's a simple aggregate query. "COUNT per UserAccount_ID grouping"

SELECT
   UserAccount_ID, COUNT(*)
FROm
   myTable
GROUP BY
   UserAccount_ID

Upvotes: 1

Related Questions