4 ms·

Because it makes no sense. If you group by one column, there might be multiple rows for a single value of the group column. And multiple rows means there might

Because it makes no sense. If you group by one column, there might be multiple rows for a single value of the group column. And multiple rows means there might be multiple, different values to choose from. And because SQL doesn't "guess" which of those you want, you are forced to tell the engine which one you want. Either by specifying an aggregate that picks exactly one (min, max, ..) or an aggregate that works on all values (avg, sum, ..)

hghsv 5y ago
I'm with you that select pay from employees group by organisation makes no sense, but that select sum(pay) from employees group by organisation would make more sense. However, it makes perfect sense to query columns which are being grouped by, something like: select organisation, pay_date, sum(amount) from payslips group by organisation, pay_date So I'm just suggesting that perhaps this could be be expressed on shorter form as the below if only implemented in some SQL dialect: select *, sum(amount) from payslips group by organisation, pay_date Is there any reason as to why that would not work? Maybe it makes no sense, but I have a hard time seeing why it wouldn't make sense.
- hans_castorp 5y ago
  Well select sum(pay) from employees group by organisation is perfectly legal (although doesn't make sense because you don't know to which organisation each sum belongs to) Regarding: select *, sum(amount) from payslips group by organisation, pay_date Remember: you get exactly one row for each unique combination of organisation and pay_date. Assuming the following table: branch | organisation | amount | pay_date -------+--------------+--------+----------- 1 | one | 10 | 2020-01-01 2 | one | 20 | 2020-01-01 4 | one | 30 | 2020-01-01 7 | one | 40 | 2020-01-01 5 | two | 50 | 2020-01-01 13 | two | 100 | 2020-01-01 11 | two | 200 | 2020-01-01 15 | two | 300 | 2020-01-01 a "group by organisation, pay_date" yields exactly two rows. Which of the values for "branch" do you want to see for organisation "one"?

hghsv 5y ago

I'm with you that select pay from employees group by organisation makes no sense, but that select sum(pay) from employees group by organisation would make more sense. However, it makes perfect sense to query columns which are being grouped by, something like: select organisation, pay_date, sum(amount) from payslips group by organisation, pay_date So I'm just suggesting that perhaps this could be be expressed on shorter form as the below if only implemented in some SQL dialect: select *, sum(amount) from payslips group by organisation, pay_date Is there any reason as to why that would not work? Maybe it makes no sense, but I have a hard time seeing why it wouldn't make sense.

hans_castorp 5y ago
Well select sum(pay) from employees group by organisation is perfectly legal (although doesn't make sense because you don't know to which organisation each sum belongs to) Regarding: select *, sum(amount) from payslips group by organisation, pay_date Remember: you get exactly one row for each unique combination of organisation and pay_date. Assuming the following table: branch | organisation | amount | pay_date -------+--------------+--------+----------- 1 | one | 10 | 2020-01-01 2 | one | 20 | 2020-01-01 4 | one | 30 | 2020-01-01 7 | one | 40 | 2020-01-01 5 | two | 50 | 2020-01-01 13 | two | 100 | 2020-01-01 11 | two | 200 | 2020-01-01 15 | two | 300 | 2020-01-01 a "group by organisation, pay_date" yields exactly two rows. Which of the values for "branch" do you want to see for organisation "one"?