Hey, I hope everyone is well, I have a doubt related to a solution. In the course Complete SQL Mastery: Summarizing data - The HAVING clause
Mosh gave an exercise, and I tried to solve it. After I saw the solution, my answer was correct, but the approaches were different, I have attached a screenshot below and that’s my solution -
As Mosh suggested, whenever we are using any aggregate function, as a rule of thumb, if we are grouping our data then we must include all the columns which are included in the SELECT clause, but I didn’t do that, please let me know your thoughts on this. Please clarify if someone is reading.
Many Thanks,
Bidhan
If we are using any aggregate function and we need to to group our data then in that case we should group our data by all the columns which are in select clause
see the screenshot below,
I think MySQL is not behaving correctly here. In your case one might think MySQL is clever enough to see that you are grouping by the primary key of the customers table so it’s OK to have customers fields in the row list.
But let’s try a query that definitely should throw an error:
SELECT name, state, city, sum(invoice_total)
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state
If we have multiple customers with invoices per state, name is ambiguous. Whose customers name should be returned?
In the sample data we only have one customer per state so maybe MySQL is again very clever and sees that it can return a valid result here?
To test that I added a another customer with state NY and a $100 invoice for that customer. The result is:
Myworks WV Huntington 101.79
Topiclounge OR Portland 980.02
Yadel CA San Francisco 705.90
Vinte NY Syracuse 902.89
Here in the last line the NY customers are summed up but name and city come from one arbitrary NY customer. IMHO MySQL should have thrown an error saying that the name and city columns are neither aggregates nor grouped.
