Counting records issue

Question

This probably is a simple one but I cant get my head around it.

I have a table of MemberBusinessCats which contains a BusinessCatID and a MemberID.. the table could be presented like this:

+-----------------------+-----------------+------------+
|  MemberBusinessCatID  |  BusinessCatID  |  MemberID  |
+-----------------------+-----------------+------------+
|  27                   |  45             |  102       |
+-----------------------+-----------------+------------+
|  28                   |  55             |  102       |
+-----------------------+-----------------+------------+
|  29                   |  61             |  102       |
+-----------------------+-----------------+------------+
|  30                   |  45             |  33        |
+-----------------------+-----------------+------------+
|  31                   |  23             |  33        |
+-----------------------+-----------------+------------+
|  32                   |  45             |  73        |
+-----------------------+-----------------+------------+
|  32                   |  61             |  73        |
+-----------------------+-----------------+------------+
|  32                   |  45             |  73        |
+-----------------------+-----------------+------------+

How do I make a script to show the following data

+-----------------+---------------------+
|  BusinessCatID  |  NumMembers In Cat  |
+-----------------+---------------------+
|  45             |  3                  |
+-----------------+---------------------+
|  55             |  1                  |
+-----------------+---------------------+
|  61             |  2                  |
+-----------------+---------------------+
|  23             |  1                  |
+-----------------+---------------------+

Many thanks in advance.

neojakey

Answer 1

You need to use an aggregate function with a GROUP BY:

select BusinessCatID, count(*) NumMembersInCat
from MemberBusinessCats
group by BusinessCatID

See SQL Fiddle with Demo

This can also be written using count() over():

select distinct BusinessCatID, 
  count(*) over(partition by BusinessCatID) NumMembersInCat
from MemberBusinessCats

See SQL Fiddle with Demo

If you want to count the number of members in each category, then you can use:

select BusinessCatID, 
  count(distinct MemberID) NumMembersInCat
from MemberBusinessCats
group by BusinessCatID

See SQL Fiddle with Demo