CODEWITHSUNDEEP
sql-servert-sql
dmall
dmall

Reputation: 45

Query: better way to implement logic

I need better logic for performance.

I need to get sum of offshore and onsite time, from the same table and the same condition, except for offshore time, I will pick only if a column chargeable is 1

select sum(onsitetime)
    + (
        select sum (offshoretime) 
        from table a
        where a.col1 = 2 and a.col2 = 4
        and a.chargeable = 1
    )
from table a
where a.col1=2 and a.col2=4

Upvotes: 0

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Use a single query with conditional aggregation:

SELECT
    SUM(onsitetime) AS sum1,
    SUM(CASE WHEN chargeable = 1 THEN offshoretime ELSE 0 END) AS sum2
FROM table_a
WHERE col1 = 2 AND col2 = 4;

This query might benefit from the following index:

CREATE INDEX ON table_a (col1, col2, chargeable);

Upvotes: 1

Related Questions