4 ms·
Similarly, I'd love some form of GROUP BY every column except for <these> It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a
by piaste 5y ago
Similarly, I'd love some form of
GROUP BY every column except for <these>
It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.
- Svip 5y agoYou know, you can just write GROUP BY x, y, ... SELECT t.i+1, count(*) FROM table t GROUP BY 1 1 in this context means the first selected item (i.e. t.i+1). I know this works in PostgreSQL.
- edoceo 5y agoI use this. It gets messy after like 10 columns. I have a trick in my ORM that copies the non-aggrigate columns into the statement. Maybe your ORM has it?
- dragonwriter 5y agoWhat I really want is a special phrase MINIMAL GROUPING such that GROUP BY MINIMAL GROUPING includes exactly the items that it would be an error not to include in the GROUP BY.
- icambron 5y agoI wish it just had a "group by all the stuff I selected without aggregation" shortcut
- xyzzy_plugh 5y agoIsn't this just SELECT DISTINCT ...?
- orthoxerox 5y agoNo. What icambron and I would like is something that can get rid of this group by: select division_name, branch_name, dealer_id, dealer_name, quarter, month, sum(total_paid) from divsions, branches, dealers, transactions where ..... --buncha joins group by division_name, branch_name, dealer_id, dealer_name, quarter, month If I didn't want to group the result by division_name, branch_name, dealer_id, dealer_name, quarter and month, why would I put them in the select clause?
- tpetry 5y agoIn PostgreSQL you can GROUP BY on the primary key so you can omit specifying all other columns
- icambron 5y agoI remember when I discovered this-- very helpful feature and I am happy it's there (and sad most DBs don't have it). But if I'm grouping I'm almost necessarily not grouping on at least one of the tables' PKs, so it only goes so far
- benjiweber 5y ago"group by 1,2,3,4,5,6" is at least a little more consise.
- Sesse__ 5y agoWhat if you do SELECT a + b, SUM(x) FROM t1? Should the implicit GROUP BY be on (a,b) or (a+b)?
- icambron 5y agoOn a+b. I'm suggesting it simply copies all the non-aggregate select pieces to the group by clause. If I want to group on a and b, I could either select them too or add them to the group by (I'm imagining something like "GROUP BY SELECT, a, b"). Note that selecting them is the natural thing you'd do if you wanted that grouping, because otherwise you wouldn't know what the grouping is when you got the results.
- coderzach 5y agoyeah, this is the right answer. Are there any examples where this isn't what you want? I guess GROUPING SETS and ROLLUP, but those could be special.
- oweiler 5y agoCan't you use an alias in a group by?
- icambron 5y agoSome DBs let you do this and it's helpful when one of the things you're selecting is some complex scalar expression, but you still have to list each item in the group by
- Sesse__ 5y agoNot by the SQL standard, since projection (which creates aliases) happens after aggregation. There are many databases that allow it as an extension, though.
- baskethead 5y agoQueries like this won’t break but will silently fail upstream by missing data if column names change or are deleted. The explicit nature of SQL ensures that it will break positively which is a better type of failure in my opinion.
- deleted 5y ago[deleted]
- Groxx 5y agowhether that's desirable or not depends on the query and what kinds of changes you make. though I could see it being error-prone in the most common cases.
- deleted 5y ago[deleted]