CODEWITHSUNDEEP
mysqlsql
Miguel Stevens
Miguel Stevens

Reputation: 9211

SQL Group by Subquery ignored

I have a database where a company has an amount of slots, These slots can be filled with persons.. I want to do a query where I can see which companies still have open slots

This is the query i'm trying but it's giving me the wrong results.

select
    name,
    slots,
    (select count(*) from persons) as persons
from companies
where city_id = 3
group by companies.id

This should give me a table with the slots, and the amount of personsfilled for that company in the persons table, but it's returning the total amount of persons every time.

This is the result

enter image description here

Thank you!

Upvotes: 0

Views: 384

Answers (1)

JimmyB
JimmyB

Reputation: 12610

Like @JoeTaras said, you need to join persons and companies to be able to tell/count which persons belong to which company. If you don't join them somehow, companies and persons will be treated and counted independently which is normally not very useful.

A different sub-query could indeed be used, but it's not quite how 'you do it', and will probably be less performant than the straight-forward join.

Example:

select
    companies.id
    companies.name,
    companies.slots,
    count(persons.id)
from companies
left outer join persons on companies.id = persons. ...
where companies.city_id = 3
group by companies.id, companies.name, companies.slots

Upvotes: 2

Related Questions