4 ms·
Ask HN: Why doesn't “select *” in grouped SQL queries yield all grouped columns?
SQL Server complains when trying to "select *" in a grouped query (unless the extreme mostly very useless case where all columns are being grouped by). It makes sense if "select *" has to mean all columns in the table (or joined tables etc) "pre grouping" (in lack of a better word). I don't know if my SQL queries are too trivial, but it seems like one would be able to merely select grouped columns or aggregate calculations on any column when grouping -- so why not let "*" refer to all the selectable columns, ie those grouped by. I believe it would improve typing speed surely, but perhaps even readability.
I know there is some dialect(s?) that let's you use \1 or similar for the first column grouped by and so forth, which I guess if a half-way solution.
Is there some reason that "select *" in grouped queries could not/should not select the columns grouped by?
- hans_castorp 6y agoBecause 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 6y agoI'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 6y agoWell 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"?
- stareatgoats 6y agoThere is I guess no reason except that it would be confusing. "select *" normally means select all the columns in the table, which is ok for ad hoc eyeballing smaller tables, where it saves you the typing involved of specifying possibly hundreds of columns - that syntax is not really ok for anything else. But you are suggesting that the same syntax should be extended (by DBMS vendors presumably) to aggregate "group by" queries, where there is hardly any such efficiency gain involved; you absolutely need to specify the "group by" columns anyway, and you can simply copy that list over into the select statement. So you would achieve more confusion, and achieve almost no efficiency. The only situation where your suggestion would make some sense would be if you routinely work with databases with tables that have many (hundreds?) of columns where a majority of these fields are included in "group by" statements. But these would be extreme edge cases in my experience. And the benefit would still be debatable.