CODEWITHSUNDEEP
sqlpostgresqlaggregate
Kal
Kal

Reputation: 988

SQL Select sum and group by two columns

I am trying to get a list of all music of the same music_id to have the SUM of price and that it was recorded on the same day

Expected output:

 music_id  | name | price | created          
-----------+--------------+------------------------
 1222      | blue | 333   | 2015-07-07
 2222      | red  | 45    | 2016-07-07
 1222      | blue | 111   | 2017-07-07

I have a table that looks like this 'music'

 music_id  | name | price | created_at           
-----------+--------------+------------------------
 1222      | blue | 111   | 2015-07-07 22:42:39
 2222      | red  | 22.5  | 2016-07-07 22:42:39
 1222      | blue | 111   | 2017-07-07 22:42:39
 1222      | blue | 111   | 2015-07-07 11:42:39
 2222      | red  | 22.5  | 2016-07-07 11:42:39
 1222      | blue | 111   | 2015-07-07 11:42:39

this is my query:

SELECT music_id, name, SUM(price), TO_DATE(cast(created_at AS text), 'YYYY-MM-DD') AS created FROM music GROUP BY music_id, created;

but I will get an error, 

ERROR:  column "music.name" must appear in the GROUP BY clause or be used in an   aggregate function

Upvotes: 0

Views: 4656

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

It's cheaper to group by the date instead of its text representation:

SELECT music_id, name, SUM(price) AS created, created_at::date AS created
FROM   music
GROUP  BY 1, 2, 4;

'YYYY-MM-DD' is the default ISO format for dates anyway. You can still force any desired format with to_char() after the cast to date: to_char(created_at::date, 'YYYY-MM-DD') - the cast only to be in sync with GROUP BY where you replace the positional reference 4 with created_at::date.

In a normalized DB schema, you would not include the functionally dependent value name in this table, just music_id (which would be a foreign key to another table with all attributes).

The query would then just group by ID and day and fetch more attributes form the other table in another join after that ...

Upvotes: 0

jimmu
jimmu

Reputation: 1056

Your select must include your grouping column. Also, you can refer to column position in group by if you are using postgres

SELECT music_id, name, TO_DATE(cast(created_at AS text), 'YYYY-MM-DD') as created, SUM(price) 
FROM music 
GROUP BY 1,2,3;

Upvotes: 3

Related Questions