CODEWITHSUNDEEP
sqlpostgresql
Pawan Kumar Sharma
Pawan Kumar Sharma

Reputation: 1168

Show two columns instead of two rows in psql

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

Upvotes: 3

Views: 817

Answers (1)

forpas
forpas

Reputation: 164099

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

Upvotes: 3

Related Questions