CODEWITHSUNDEEP
postgresqlgroup-by
Arun
Arun

Reputation: 2478

PostgreSQL group by error

I have a relation in a PostgreSQL database called 'processed_data' having the following schema:

Date -> date type, shop_id -> integer type, item_category_id -> integer type, sum_item_cnt_day -> real type.

Displaying the first 5 rows of the relation is as follows:

date  |  shop_id  |  item_category_id  |  sum_item_cnt_day

------+-----------+--------------------+------------------

2014-12-29  |  49  |  3  |  4

2014-12-29  |  49  |  6  |  1

2014-12-29  |  49  |  7  |  1

2014-12-29  |  49  |  12  |  3

2014-12-29  |  49  |  16  |  1

Now, the 'shop_id' has 60 unique shops ranging from 0-59 where each shop sells some items grouped to a new column 'item_category_id' where 'sum_item_cnt_day' denotes the number of items sold by a shop and it's item_category_id.

I am now trying to further aggregate the data by just trying to get the following columns as final result-

date, shop_id, sum_item_cnt_day

So that, data is aggregated according to number of all items sold in 'item_category_id' per shop (denoted by 'shop_id') and calculating sum of 'sum_item_cnt_day'.

When I try to execute the following SQL command-

select date, shop_id, sum(sum_item_cnt_day) from processed_data group by shop_id;

It gives the error-

ERROR: column "processed_data.date" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select date, shop_id, sum(sum_item_cnt_day) from processed_d... ^

Even the following SQL command-

select date, shop_id, sum(sum_item_cnt_day) from processed_data where date between '2013-01-01' and '2013-01-31' group by shop_id;

Gives the error-

ERROR: column "processed_data.date" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select date, shop_id, sum(sum_item_cnt_day) from processed_d... ^

Any suggestions as to what's going wrong and what am I missing?

Thanks!

Upvotes: 0

Views: 56

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

The simplest fix, which is what I think you want, would be to just add date to the GROUP BY clause:

SELECT date, shop_id, SUM(sum_item_cnt_day)
FROM processed_data
GROUP BY date, shop_id;

If you really don't want sums taken for each shop on each day, but rather for each shop over all days, then you will have to think of which of the many dates you want to display.

Upvotes: 1

Related Questions