Python & PostgreSQL problem with GROUP BY

Question

I have got some problems with function view(). I received error:

column "store.id" must appear in the GROUP BY clause or be used in an aggregate functio

For instance:

name    number       quantity
plc        55              1
engine     66              1
plc        55              3

OUTPUT:

name    number       quantity
plc        55              4
engine     66              1

This is sort of a fundamental misunderstanding of aggregation in SQL queries. But to help, we need to know what you want your output to look like. For example, when using `GROUP BY` the results will be one record per group -- but there may be many table rows that "fall into" that group. How do you want them displayed? (Consider `id`, there may be many `id`s that all match your criteria and end up in the same group -- do you want them all combined into one "cell"?) — jedwards, Mar 13 '19 at 18:34
re: your update: `SELECT name, number, SUM(quantity)` and group by the columns that you aren't aggregating (which are name and number) -- so `GROUP BY name, number`. Selecting anything else will require you to either add it to the list in `GROUP BY` or aggregate it (as you with `SUM(quantity)`). — jedwards, Mar 13 '19 at 18:54
Is any chance to received also an ID without grouping by it? — glaadys, Mar 13 '19 at 19:02

Andreas Lorenzen · Answer 1 · 2019-03-19T20:21:20.537

I imagine that you are looking for something like this:

SELECT
  name,
  producent,
  model,
  number,
  SUM(quantity),
  warehouse,
  location
FROM store
WHERE
  quantity > 0
GROUP BY
  name,
  producent,
  model,
  number,
  warehouse,
  location
ORDER BY
  number;

I removed id from the selected fields, since you are already declaring that as a UNIQUE (PRIMARY KEY) field in the CREATE TABLE statement earlier (becauase it is autoincremented by the sequence you are declaring). You never want to group on a UNIQUE field - it will just give you the same results as without it.

But in order to be certain, we need to know what you want your output to look like.

A rule of thumb here, when it comes to grouping results with SQL is this: Always include all the fields that are selected outside of an aggregate function in the GROUP BY clause.

Update

You have edited your question, so that the original query you included is gone. But it looks like you have included the results that you want instead. Here's how you would get those:

SELECT
  name,
  number,
  SUM(quantity) as "quantity"
FROM store
WHERE
  quantity > 0
GROUP BY
  name,
  number
ORDER BY
  number;

I hope you can accept the answer like this.

Hi! I hope the answer was helpful. Please let me know if there is something else I can help with in respect to this - or perhaps accept the answer? — Andreas Lorenzen, Mar 26 '20 at 09:14

Zaynul Abadin Tuhin · Answer 2 · 2019-03-13T18:55:14.627

0

change your query like below

SELECT  name,  number,
 SUM(quantity) FROM store WHERE quantity>0 
 GROUP BY name,  number  ORDER BY number

you have to put non aggregated selected column into group by also

edited Mar 13 '19 at 18:55

answered Mar 13 '19 at 18:37

Zaynul Abadin Tuhin

31,407
5
33
63

I'm not sure what the point of grouping is if you're going to include the primary key in the list of grouped-by columns... – jedwards Mar 13 '19 at 18:38
@jedwards answer edited as you edited your question i mentioned that you have to put column name in group by those are selected but non aggregated – Zaynul Abadin Tuhin Mar 13 '19 at 18:56

score 0 · Answer 3 · answered Mar 13 '19 at 18:40

By default the columns in select are expected in group by clause. You can include all the columns in select also in group by as mentioned above. I also see an alternative where a sub query can be used to avoid including all columns in group by. Please refer this link- previous answer for related issue

Python & PostgreSQL problem with GROUP BY

3 Answers3

Update