14

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.

  • 4
    As nobody explained why your query doesn't return the expected result: As aggregate function ignore NULLs you must count a column from the inner table (you counted from the outer table) which is known to be defined as NOT NULL (to be able to distinguish between NULLs within the data and NULL created by the Outer Join). The easiest way is to count the join column: COUNT(u.streetid) – dnoeth May 26 '17 at 18:00
  • Because right join and right outer join are same things. I did add an explanation in my answer as suggested by @jpmc26 . – SqlWorldWide May 30 '17 at 19:42
18

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

| improve this answer | |
  • 2
    For me, changing from count(*) to count(customer.id) - similar to shown above - made the critical difference. Thanks :) – Zeek Feb 15 at 7:57
10

This is one possible way.

select s.name as streets,
       (select count(*)
        from   users
        where  StreetID = s.id) cnt
from   streets s;
| improve this answer | |
7

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
| improve this answer | |
0
  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
| improve this answer | |
  • That is with left join – rakesh Sep 11 '19 at 17:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.