How to use condition and aggregation in postgresql

Question

I tried to use a select query in which contains both where and sum aggreation query.But its shows error while executing the query . Below is sample table

|sensorid | timestamp  | reading  |
====================================
|1        | 1604192522 | 10       |
|1        | 1604192525 | 15       |
|2        | 1605783723 | 8.1      |

My query is

select date_trunc('day', v.timestamp) as day,sum(reading) from sensor  v(timestamp,sensorid) group by  (DAY) having sensorid=1;

while executing below error occured

Cannot use column sensorid outside of an Aggregation in HAVING clause. Only GROUP BY keys allowed here.]

Tomáš Záluský · Accepted Answer · 2020-11-24 09:16:26Z

1

If you apply group by, you cease particular values of all other columns. Probably you want to either filter by values -> use where

select date_trunc('day', v.timestamp) as day,sum(reading) from sensor  v(timestamp,sensorid) where sensorid=1 group by  (DAY) ;

or filter by aggregation -> keep having but use aggregation function

select date_trunc('day', v.timestamp) as day,sum(reading) from sensor  v(timestamp,sensorid) group by  (DAY) having min(sensorid)=1;

Not clear what's your intention, post expected output if I didn't guess any variant.

answered Nov 24, 2020 at 9:16

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use condition and aggregation in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related