How to get a group where the count is zero?
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
This is one possible way.
select s.name as streets,
(select count(*)
from users
where StreetID = s.id) cnt
from streets s;
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