Ordering before aggregate function causes error

Feedback and Requests
1

Maybe this issue has already been reported but I couldn’t find it. I’m not sure if this is a bug or a feature request.

Found an issue with metabase with postgres (possibly with other databases aswell). Using an aggregate function (sum, count etc…) on an ordered table causes an error:
org.postgresql.util.PSQLException: ERROR: column XXX must appear in the GROUP BY clause or be used in an aggregate function

From SQL point of view this is an expected result but from a user point of view I would expect aggregate function to automatically lose the ordering, especially since I can’t find an easy way to make the table not be ordered.

2

Help me a bit more understand the context. What is column XXX - the one you are are aggregating on or how does it come into play?

If it’s a previously applied sorting you need to get rid of it’s a bit — too —well hidden under the ... to the right in the search bar. Is that simply the UX beef you’re running into? (which I BTW agree could be better)

3

In my example it was a date field.

For example a table:
id created name amount

I added a a created filter (between Jan 1 - Apr 30 2018), ordered by created to see data in logical order.
Then wanted to see the combined sum of amounts for this period but ran into this error. It works if I don’t order by created field.

4

Hi, could you share your syntax?

5

Ok thanks!

Got you now with that extra info, and agree. It has bitten me as well. I’d call it a UX bug. Question is then what guidance we would like to have here:

  1. Silently drop any previously applied sorting that doesn’t have a grouping (as you suggest)
  2. Prompt/warn the user if they want to drop the sorting - or if they want to use it to group by
  3. …?
6

Good to see you found a way to replicate this issue, I should probably have been a bit more exact with the description!

As you say there could be a couple of solutions but most importantly it is a preventable error I believe. In my case I would prefer the ordering to automatically disband (probably should go back once you switch back to raw data view) but maybe there’s a better solution as a whole.
At the moment this is a strange error for people who do not understand SQL and they probably won’t find a solution without technical help.

2 Likes