How do I convert this SQL Server query to PostgreSQL?-CodePudding

Please I need help. How do I convert this SQL Server query to PostgreSQL? Thank you.

SELECT DISTINCT
    DATENAME('m', started_at)   ' '   CAST(DATEPART(yyyy, started_at) AS varchar) AS year_month,
    bike_type,
    user_type,
    COUNT(*) AS no_of_rides
FROM
    Final_Data
GROUP BY
    DATENAME('m', started_at)   ' '   CAST(DATEPART(yyyy, started_at) AS varchar),
    bike_type, user_type

CodePudding user response：

SELECT
    to_char(started_at,'YYYY-MM') year_month, 
    bike_type,
    user_type,
    count(*) AS no_of_rides
FROM
    Final_Data
GROUP BY
    1,2,3;

CodePudding user response：

You can use to_char(started_at, 'YYYY-MM') to extract the year and month.

select distinct 
  to_char(started_at, 'YYYY-MM')
    as year_month, 
  bike_type,user_type,count(*)
     as no_of_rides
from Final_Data
group by 
  to_char(started_at, 'YYYY-MM'),
  bike_type,  
  user_type;