How to Get a Group with Zero Count in SQL Server

group byjoin;sql server

I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero.

For this I've tried this query:

Create table Streets(
  ID int IDENTITY  primary key,
  Name varchar(100)
);

create table users(
  ID int IDENTITY  primary key,
  Username varchar(100),
  StreetID int references Streets(id)
);

insert into streets values ('1st street'), ('2nd street'), ('3rd street'), 
                           ('4th street'), ('5th street');
insert into users values ('Pol', 1), ('Doortje', 1), ('Marc', 2), ('Bieke', 2), 
                         ('Paulien', 2), ('Fernand', 2), ('Pascal', 2), ('Boma', 3), 
                         ('Goedele', 3), ('Xavier', 4);

select s.name as street, count(s.name) as count 
from users u inner join streets s on u.streetid = s.id
group by s.name

And it gives me this output:

|   | street     | count |
| - | ---------- | ----- |
| 1 | 1st street | 2     |
| 2 | 2nd street | 5     |
| 3 | 3rd street | 2     |
| 4 | 4th street | 1     |

The problem is that the 5th street, where no user lives, doesn't appear on the result. Could I do this with SQL server? Here you've got a fiddle

Update: If I do right join, I've got this result:

|   | street     | count |
| - | ---------- | ----- |
| 1 | 1st street | 2     |
| 2 | 2nd street | 5     |
| 3 | 3rd street | 2     |
| 4 | 4th street | 1     |
| 5 | 5th street | 1     |

See this fiddle.

Best Answer

The reason your query did not work as intended:

Inner join gives you the intersection of 2 tables. In your case, there was no entry for 5th street in your users table and that is why join did not produce any entry for that.

Outer join (right or left) will give the result of inner join and in addition all non-qualifying records from the left or right table depending on the type (left or right) of outer join.

In this case, I put Street on the left of the join and used left outer join as you wanted all streets (even count is zero) in your result set.

Change your select query to this.

SELECT S.Name AS Street,
       Count(U.Username) AS COUNT
FROM Streets S
LEFT OUTER JOIN Users U ON U.Streetid = S.Id
GROUP BY S.Name

Result enter image description here

Related Question