Search code examples
sqlpostgresqlodooodoo-11

select column twice of same table on different condtion Psql

I'm working on Odoo11 and using psql. And I want to select the same column twice for the same table on a different condition, For this, I'm using case statement And I got the output, but not as expected. Because I want output in a single row but it comes in two rows.

Query

 SELECT project_id,
    CASE WHEN project_type = 'Internal'  THEN SUM(unit_amount) END,
    CASE WHEN project_type = 'Project' THEN SUM(unit_amount) END 
FROM account_analytic_line where project_id = 347 GROUP BY project_id,project_type

Output Screenshot: enter image description here

For above screenshot, it generates two rows for the same project_id and wants these columns in a single row for the same project_id.

Thanks in advance

Solution

  • to get the result in a single row, you can use window functions:

    select distinct on (project_id)
project_id,
sum(unit_amount) FILTER (where project_type = 'Internal') over(PARTITION BY project_id) as intern, 
sum(unit_amount) FILTER (where project_type = 'Project') over(PARTITION BY project_id) as project
FROM account_analytic_line

    extend with a fitting where clause.