select column twice of same table on different condtion Psql

Odoo is the world's easiest all-in-one management software.
It includes hundreds of business apps:

CRM
e-Commerce
Accounting
Inventory
PoS
Project management
MRP

Take the tour

Posts People Badges

Tags View all

odoo accounting v7 pos invoice

About this forum

Help

select column twice of same table on different condtion Psql

3 Replies

4448 Views

postgresql odoo psql odoo11

Pawan Kumar Sharma

Hello All,

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

Now result coming like that:

project_id integer	case double_precision	case double_precision
347	7.16666666667
347		33.58333333333

Require Result:

project_id integer

case double_precision

347

7.16666666667

33.58333333333

For the above screenshot, it generates two rows for the same project_id and wants these columns in a single row for the same project_id. Project type is the selection field with two options(Project, Internal)

Thanks in advance

Pawan Kumar Sharma

Author Best Answer

I got solution for my problem:

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 where project_id = 347

Thanks to all

Usman Farzand

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

pls use this

Pawan Kumar Sharma

Author

Hello Usman, it still take two rows. I want it only in single row.

Pawan Kumar Sharma

Author

I added the expected table in the above problem.

Adil Akbar

Hi, you can follow this for using PSQL commands using python: https://youtu.be/ntMaAYbsDYM

Hope it helps

This question has been flagged