CODEWITHSUNDEEP
sqlpostgresql
Waqas
Waqas

Reputation: 639

Raw SQL query looks odd, looking for a better way

I rarely had the chance to write RAW SQL query, I wrote this query but feel like something is not right about it. I wonder if there is a better way to write it. Maybe I will learn something new.

select a.approved, c.review, d.pending, f.total, f.total - a.approved - c.review as ongoing
from
 (select count(distinct sownumber) as approved from v_scope_of_work_combined where name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and sow_status = 'PM Approved'  and implementer = 'C1') as a,
 (select count(distinct sownumber) as review from v_scope_of_work_combined where name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed'and sow_status = 'PM Approved'  and implementer = 'C1') as c,
 (select count(distinct sownumber) as pending from v_scope_of_work_combined where name = 'CTN-Approve' and v_scope_of_work_combined.design_readiness = 'No'and sow_status = 'PM Approved'  and implementer = 'C1') as d,
 (select count(distinct sownumber) as total from v_scope_of_work_combined where  sow_status = 'PM Approved'  and implementer = 'C1') as f;

Upvotes: 0

Views: 49

Answers (3)

Michal T
Michal T

Reputation: 611

I think writing this kind of queries without several sub-queries is more readable.

SELECT 
    COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
        AS approved,
    
    COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed'and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
        AS review,
    
    COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and v_scope_of_work_combined.design_readiness = 'No'and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
        AS pending,
    
    COUNT(DISTINCT CASE WHEN sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) 
        AS total,
    
    COUNT(DISTINCT CASE WHEN sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END) AS pending
        - COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END)
        - COUNT(DISTINCT CASE WHEN name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed'and sow_status = 'PM Approved'  and implementer = 'C1' THEN sownumber ELSE NULL END)
        AS ongoing
FROM
    v_scope_of_work_combined

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can write conditional aggregation as follows:

select approved, review, pending, total, total - approved - review as ongoing
from (
  select count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed' and implementer = 'C1' and sow_status = 'PM Approved') as approved,
         count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed' and implementer = 'C1' and sow_status = 'PM Approved') as review,
         count(distinct sownumber) filter (where name = 'CTN-Approve' and design_readiness = 'No' and implementer = 'C1' and sow_status = 'PM Approved')  as pending,
         count(distinct sownumber) filter (where implementer = 'C1' and sow_status = 'PM Approved') as total 
  from v_scope_of_work_combined t) t

Upvotes: 0

user330315
user330315

Reputation:

You can improve this by using filtered aggregation:

select approved, review, pending, total, total - approved - review as ongoing
from (
  select count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks = 'NotClaimed') as approved,
         count(distinct sownumber) filter (where name = 'CTN-Approve' and assigned_engineer_my_tasks != 'NotClaimed') as review,
         count(distinct sownumber) filter (where name = 'CTN-Approve' and design_readiness = 'No' )  as pending,
         count(distinct sownumber) as total 
  from v_scope_of_work_combined 
  where implementer = 'C1'
    and sow_status = 'PM Approved'
) t

Upvotes: 2

Related Questions