CODEWITHSUNDEEP
sqlpostgresqlgroup-by
hky404
hky404

Reputation: 1159

unable to typecast timestamp to date in Group By

I am unable to typecast timestamp to date type in the Group By of my SQL Select statement.

SELECT geography_id,
         listed_at::DATE,
         EXTRACT(YEAR FROM listed_at) AS year,
         EXTRACT(MONTH FROM listed_at) AS month,
         EXTRACT(day FROM listed_at) AS day,
         Count(*) AS active_listing_count,
         SUM(list_price) AS sum_of_listing_price,
         Date_part('day', current_date :: timestamp - listed_at :: timestamp) AS days_on_market,
         COUNT(num_bathrooms) AS total_bathrooms,
         COUNT(num_bedrooms) AS total_bedrooms
  FROM   properties
  WHERE  expired_at IS NULL
  GROUP  BY geography_id, 
            listed_at::DATE
  ORDER  BY listed_at::DATE DESC;

I am getting this error:

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

Upvotes: 0

Views: 38

Answers (1)

klin
klin

Reputation: 121654

Each occurrence of listed_at in select list should be casted to date:

SELECT geography_id,
         listed_at::DATE,
         EXTRACT(YEAR FROM listed_at::date) AS year,
         EXTRACT(MONTH FROM listed_at::date) AS month,
         EXTRACT(day FROM listed_at::date) AS day,
         count(*) AS active_listing_count,
         SUM(list_price) AS sum_of_listing_price,
         date_part('day', current_date::timestamp - listed_at::date) AS days_on_market,
         COUNT(num_bathrooms) AS total_bathrooms,
         COUNT(num_bedrooms) AS total_bedrooms
  FROM   properties
  WHERE  expired_at IS NULL
  GROUP  BY geography_id, 
            listed_at::DATE
  ORDER  BY listed_at::DATE DESC;

Upvotes: 2

Related Questions