5 ms·
I wish it just had a "group by all the stuff I selected without aggregation" shortcut
by icambron 5y ago
I 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.