in

Redshift SQL – Group by Error when using DATE() or Trun()


I am trying to get the count of appointments on a given day, on a given ServiceResource. Each of these values are stored in different tables requiring me to use joins. The code I am using is below:

select
trunc(s.actualstarttime) "Delivery Date",
s2.name "Van Name", 
count(a.serviceid) "Stops"
from
assignedresource a
join serviceappointment s on s.id = a.serviceid
inner join serviceresource s2 on s2.id = a.serviceid
where
s.status="Completed"  and
s.actualstarttime > '2021-12-20 00:00:00' and s.actualstarttime < '2021-12-23 23:00:00'
group by 1, 2
order by
actualstarttime desc;

My expected output should give me a table like this:

2021-12-23 | Van A | 3

2021-12-23 | Van B | 4

2021-12-22 | Van A | 5

But I keep getting this error :
SQL Error [500310] [42803]: Amazon Invalid operation: column “s.actualstarttime” must appear in the GROUP BY clause or be used in an aggregate function;

I have tried using
group by trunc(s.actualstarttime), s2.name as well. Can someone help me figure out why or how can I get around this?



Source: https://stackoverflow.com/questions/70629148/redshift-sql-group-by-error-when-using-date-or-trun

Pinki helps developers ship software with authenticity

A Daily Progress Tracker App Built Using React