PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Transforming MariaDB sql to Posgresql

I have the following sql code for MariaDB and it is working, but now I try to rewrite it into to sql for PostgreSQL and get an error.

The sql code for MariaDb is:

SELECT monthname(f.datetime) AS month, 
sum(case when f.amount >= 0 then f.amount else 0 end) AS income, 
sum(case when f.amount < 0 then f.amount else 0 end) AS expense, 
sum(f.amount) as difference 
from finance.finance f
where f.country not in ("ZZ")
and year(f.datetime) = $year
GROUP BY monthname(f.datetime)
order by month(f.datetime)

and the code in sql in PostgreSQL is:

select to_char(f.datetime, 'Month') as month, 
sum(case when f.amount >= 0 then f.amount else 0 end) as income, 
sum(case when f.amount < 0 then f.amount else 0 end) as expense, 
sum(f.amount) as difference 
from finance f
where f.country not in ('ZZ')
and extract(year from f.datetime) = $year
GROUP BY to_char(f.datetime, 'Month')
order by to_char(f.datetime, 'MM')

But I get the following error:
ERROR: column "f.datetime" must appear in the GROUP BY clause or be used in an aggregate function

The following sql code works but gives the month as a number with a leading zero.

select to_char(f.datetime, 'MM') as month, 
sum(case when f.amount >= 0 then f.amount else 0 end) as income, 
sum(case when f.amount < 0 then f.amount else 0 end) as expense, 
sum(f.amount) as difference 
from finance f
where f.country not in ('ZZ')
and extract(year from f.datetime) = $year
GROUP BY to_char(f.datetime, 'MM')
order by to_char(f.datetime, 'MM')

How can I solve it? I want to have the first column as month name, thus no month numbers or timestamps.

Tags:

2

Answers


  1. 0

    You could use a subselect:

    select month_text, income, expense, difference from (
 select 
  to_char(f.datetime, 'Month') as month_text, 
  extract(month from f.datetime) as month_num,
  sum(case when f.amount >= 0 then f.amount else 0 end) as income, 
  sum(case when f.amount < 0 then f.amount else 0 end) as expense, 
  sum(f.amount) as difference 
 from finance f
 where f.country not in ('ZZ')
  and extract(year from f.datetime) = $year
 GROUP BY to_char(f.datetime, 'Month'), extract(month from f.datetime)
) t
order by month_num
    Login or Signup to reply.
  2. 0

    you can simply add the MM t6o the GROUP BY it wouldn’t change the result and so you use it to ORDER BY

    CREATE tABLE finance (datetime timestamp, amount int, country text)

    CREATE TABLE

    select to_char(f.datetime, 'Month') as month, 
sum(case when f.amount >= 0 then f.amount else 0 end) as income, 
sum(case when f.amount < 0 then f.amount else 0 end) as expense, 
sum(f.amount) as difference 
from finance f
where f.country not in ('ZZ')
and extract(year from f.datetime) = 2021
GROUP BY to_char(f.datetime, 'Month'), to_char(f.datetime, 'MM')
ORDER BY to_char(f.datetime, 'MM')
    month income expense difference 
    SELECT 0

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search