CODEWITHSUNDEEP
sqlsql-server
techora
techora

Reputation: 619

Join count as a column in the sql query

I have two tables Business and BusinessCat. I need a MSSQL Query that will get me the following result:

ID | CategoryName | RecordCount
1  | Local        | 3
2  | National     | 1
3  | International| 2
4  | Other        | 0

I need the count of how many there are in each category as another column.

Business table looks like this:

ID | Category | BusinessName
1  | 3        | Blackstone, Inc.
2  | 2        | Pet Smart
3  | 1        | John Doe
4  | 3        | Best Buy
5  | 1        | Sams Treats
6  | 1        | Eastcoast Tattoo

BusinessCat table looks like this:

ID | CategoryName
1  | Local
2  | National
3  | International
4  | Other

Upvotes: 1

Views: 7188

Answers (6)

DRapp
DRapp

Reputation: 48139

select
      BC.ID,
      BC.CategoryName,
      coalesce( count(B.ID), 0 ) as RecordCount
   from
      BusinessCat BC
         LEFT JOIN Business B
            on BC.ID = B.Category
   group by
      BC.ID,
      BC.CategoryName
   ORDER BY
      BC.ID

You need a LEFT-JOIN to the business categories table to allow that one entry of no records to remain showing in the final list.

Upvotes: -1

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Well, got it wrong, should be (like other people stated):

SELECT bc.id, bc.categoryname, COUNT(b.id)
  FROM businesscat bc LEFT JOIN business b ON (bc.id = b.category)
GROUP BY bc.id, bc.categoryname
;

Upvotes: 0

Kaf
Kaf

Reputation: 33809

You need a Left/Right Join as:

SELECT CN.ID, CN.CategoryName, isnull(Count(B.ID),0) RecCount
FROM Business B
RIGHT JOIN BusinessCat CN
              ON B.Category = CN.ID
GROUP BY CN.ID, CN.CategoryName

Upvotes: 0

Szymon
Szymon

Reputation: 43023

SELECT CN.ID, CN.CategoryName, ISNULL(Count(B.ID), 0) AS RecordCount
FROM Business B
RIGHT JOIN BusinessCat CN
ON B.Category = CN.ID
GROUP BY CN.ID, CN.CategoryName

or

SELECT CN.ID, CN.CategoryName,
    (SELECT COUNT(*) FROM Business B WHERE B.Category = CN.ID) AS RecordCount
FROM BusinessCat CN

Check which query is faster.

Upvotes: 5

Tim Schmelter
Tim Schmelter

Reputation: 460138

You need a LEFT OUTER JOIN + Group By + Count:

SELECT bc.ID, bc.CategoryName, RecordCount = COUNT(b.ID)
FROM BusinessCat bc LEFT OUTER JOIN Business b
    ON bc.ID = b.Category
GROUP BY bc.ID, bc.CategoryName

Demonstration

Upvotes: 0

Kris
Kris

Reputation: 9

you can try:

select bc.id, bc.cat_name, count(b.cat)
 from Business b (nolock)
inner join businesscat bc (nolock)
on b.cat=bc.id
group by bc.id, bc.cat_name

Upvotes: 0

Related Questions