Show two columns instead of two rows in psql

user_7653430 0
Viewed 298

I am using the query shown here, and it shows two rows due to project_type is selection (Internal, Project) column.

My requirement is to show sum(unit.amount) two columns for project_type - instead of two rows.

SELECT project_id, project_type, SUM(unit_amount)
FROM account_analytic_line
WHERE project_id = 2
GROUP BY project_type, project_id

Current result:

469;"Internal";154.416666666667
469;"Project";3963.08333333333

Required result:

469; 154.416666666667; 3963.08333333333
postgresql sql
1 Answers

You are getting 2 rows because you use project_type in the GROUP BY clause.
You must group by project_id only and use conditional aggregation:

SELECT project_id, 
       SUM(CASE WHEN project_type = 'Internal' THEN unit_amount ELSE 0 END) sum_Internal, 
       SUM(CASE WHEN project_type = 'Project' THEN unit_amount ELSE 0 END) sum_Project
FROM account_analytic_line
WHERE project_id = 2
GROUP BY project_id
user_104988280
Related