How to display 0 in a graph?
How can I display 0 if there is no qualification match?
My current request:
select s.sname, count(*) as number
from sailor s, boat b
where s.rating >= b.rating
group by s.sname
order by number desc;
And I got the result:
a 3
b 3
c 2
However, this is not really what I am trying to achieve. I would like to show the result like this:
a 3
b 3
c 2
d 0
e 0
I'm trying to change count () to isnull (count (), 0) or coalesce (count (*), 0), but in this case it doesn't adjust.
CREATE TABLE sailor (
sname character varying primary key,
rating integer
);
create table boat (
bname character varying primary key,
color character varying,
rating integer
);
+3
source to share
2 answers
You are using an inner join that only returns rows when the data in both tables is the same.
To return sailors without a suitable boat, use an outer join :
select s.sname, count(*) as number
from sailor s
left join boat b on s.rating >= b.rating
group by s.sname
order by number desc;
+1
source to share