Hacker News Reader

Comment by piaste | Hacker News Reader

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.

View on HN

Queries 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.

whether 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.

I wish it just had a "group by all the stuff I selected without aggregation" shortcut

Isn't this just SELECT DISTINCT ...?

No. 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?

"group by 1,2,3,4,5,6" is at least a little more consise.

What if you do SELECT a + b, SUM(x) FROM t1? Should the implicit GROUP BY be on (a,b) or (a+b)?

On 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.

In PostgreSQL you can GROUP BY on the primary key so you can omit specifying all other columns

I 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

yeah, 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.

You 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.

I 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?

What 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.

Can't you use an alias in a group by?

Not by the SQL standard, since projection (which creates aliases) happens after aggregation. There are many databases that allow it as an extension, though.

Some 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

Reply on news.ycombinator.com