How to get a group where the count is zero?

Question

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:

ID	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:

ID	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.

Answer 1

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

Answer 2

This is one possible way.

select s.name as streets,
       (select count(*)
        from   users
        where  StreetID = s.id) cnt
from   streets s;

Answer 3

Cleaning up code to work on a case sensitive instance...

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 );

When you use COUNT with a column name, it counts NOT NULL values.

I'm using a RIGHT JOIN here to appease Joe Obbish.

SELECT   s.Name AS street, COUNT(u.Username) AS count
FROM     users AS u
RIGHT JOIN Streets AS s
ON u.StreetID = s.ID
GROUP BY s.Name

Results:

street	count
1st street	2
2nd street	5
3rd street	2
4th street	1
5th street	0

Answer 4

1. Get the count by street id
2. join the street id with id from streets
3. Use Coalsesce as the null value will result

Here is the short query:

select Name, coalesce( u.ct,0)ct FROM streets s left join (
select StreetID,count(*)ct from users group by StreetID)u on s.ID=u.StreetID