Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I want to display number of employees working for every company, even if that number is zero.

for e.g.

Company : 
Microsoft Bangalore
IBM       NY
works : 
emp1 Microsoft 10000
emp2 Microsoft 90000
output should be : 
Microsoft 2
IBM 0
But the following query and other similar queries print only
those companies which have at least one employee :
Select count(*) from works natural join company group by company.cname
If I use outer join, then the companies with zero employees will still show
up in one row, so that option is out as well.
How to do it?
                P.S.: You should dump NATURAL JOINs. They seem to be handy, but they are not really useful, IMHO. They water down your expressive capabilities and they make implications on how to design your database (column naming), which is a big WTF for me.
– Tomalak
                Apr 13, 2009 at 9:50
Select company.cname, count(work.id) from company left join work on ....=.... group by company.cname
where you fill out the "...." parts, and change the work.id to you name
There is another way to do so with subquery, here is a sample from my case:
select count(`order_id`) as cnt
from (
    select `order_id` from `room_bookings`
    where `room_id` = 3 and `day_id` = 20180201 
    group by `order_id`
) as b;
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.