0

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!

Miguel Stevens
  • 8,631
  • 18
  • 66
  • 125
  • 2
    You must link persons table with compianies table or another to know if person use a slot. I think you must review your table structure. – Joe Taras Oct 31 '14 at 09:30
  • show the table structure of both the tables and your query will return same number since you are counting data from persons table each time. – Abhik Chakraborty Oct 31 '14 at 09:31

1 Answers1

2

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
JimmyB
  • 12,101
  • 2
  • 28
  • 44
  • Thank you very much! It's also working with only: GROUP BY companies.id.. How come this query is working? i shouldn't of used a subquery then? Thanks! – Miguel Stevens Oct 31 '14 at 09:34
  • 1
    MySQL [Extends the GROUP BY clause](http://stackoverflow.com/a/7596265/1048425) to allow columns in the select list that are not contained in the group by. If `companies.ID` is the primary key (this seems logical) then only grouping by this is perfectly legitimate since all other fields in `companies` are functionally dependent on this. The problem with this extension arises when you abuse it, for example if you only group by `companies.name`, and select `companies.id` - if you have two companies with the same name and different IDs, then the value returned for `ID` is not deterministic. – GarethD Oct 31 '14 at 09:40
  • Also +1 for the correct answer, however it would be much better to provide even a brief explanation of what you have done to fix the problem and why. Code only answers have limited use, and don't address the real purpose of Stackoverflow as a question and answer site, not a free code writing service. As the old proverb goes, "give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime" – GarethD Oct 31 '14 at 09:43