Hacker News Reader

Comment by xyzzy_plugh | Hacker News Reader

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?

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

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

Reply on news.ycombinator.com