CODEWITHSUNDEEP
sqlsql-server
Josiah Thames
Josiah Thames

Reputation: 45

Include a column to count records with a specific value

I want to return all data in a table and append a column that counts the number of records in a subset (say, the number of houses in a neighborhood).

I tried

CASE
    WHEN EXISTS (SELECT 1 as [parcels]
        FROM dbo.parcels p2
        WHERE p2.Neighborhood = p.Neighborhood)
    THEN COUNT([parcels]) END  -- can't count outside subquery
        as [TotalProps]

The subquery itself returns a value of 1 for each property record in any given neighborhood, but I can't count/sum the [parcels] outside of the subquery in a THEN statement.

Input Table:

dbo.parcels
ID    Address        Neighborhood
==    =======        ============
1     123 Main St    MITO
2     124 Main St    MITO
3     200 2nd St     MITO
4     201 2nd St     MITO
5     5 Park Ave     FAIRWIND
6     1600 Baker St  GALLERY
7     1601 Baker St  GALLERY
8     1602 Baker St  GALLERY

SELECT *, <<<COUNT(neighborhood props)>>> as [TotalProps]
FROM dbo.parcels p

Expected Output:

ID    Address        Neighborhood  TotalProps
==    =======        ============  ==========
1     123 Main St    MITO          4
2     124 Main St    MITO          4
3     200 2nd St     MITO          4
4     201 2nd St     MITO          4
5     5 Park Ave     FAIRWIND      1
6     1600 Baker St  GALLERY       3
7     1601 Baker St  GALLERY       3
8     1602 Baker St  GALLERY       3

Upvotes: 1

Views: 45

Answers (3)

Excelnoobster
Excelnoobster

Reputation: 129

Keeping things simple - a basic subselect will give you what you need ...

SELECT
p.*,
(
    select count(*)
    FROM dbo.parcels p2
    WHERE p2.neighborhood = p1.neighborhood ) AS hoodcount
FROM dbo.parcels p

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Use window functions:

select p.*, count(*) over (partition by neighborhood)
from dbo.parcels p;

Upvotes: 1

Piotr Palka
Piotr Palka

Reputation: 3159

You can use COUNT OVER PARTITION aggregate:

SELECT
   p.*,
   COUNT(ID) OVER(PARTITION BY Neighborhood) AS TotalProps
FROM dbo.parcels p

Upvotes: 2

Related Questions