Question:I have two simple tables:
My purpose is to get a table which shows the ratio between number of visitors in the hotel to number of apples consumed by that time.
For the example above the desired query output should look like this:
|2022-01-01||2 –>(visitors #1, #2)||5|
|2022-02-01||3 –>(visitors #1, #2, #3)||7|
|2022-03-01||3 –>(visitors #2, #3, #4)||2|
If I were to write a solution to this task using code I would go over each
apples_consumptiontable and count how many visitors have a lower/equal
report_dateand also have a
check_out_date= NULL or
check_out_dategreater/equal than that
I came up with this query:
I am looking for a more efficient way to achieve this result and your help will be highly appreciated!
Answer:It is very rarely a good idea to put a subselect in your select list.
Join your tables and then use an aggregate count:
If you have better answer, please add a comment about this, thank you!