Skip to content
Advertisement

Count(*) of two sql statements where results are subset of another one

I have a table which has ~500 GBs of data and have two queries running on it.

-- Query 1
select Count(*) from table
where C1 = A

-- Query 2
select Count(*) from table
where C1 = A and C2 = B

I feel Query 2 execution on whole table is un-necessary as the results are subset of Query 1. Is there any optimized way to first execute Query 1 then run Query 2 on the results of it and finally return Count of both the results.

Advertisement

Answer

SELECT
    COUNT(*) AS cnt_1,
    SUM(c2 = 'B') AS cnt_2
FROM yourTable
WHERE c1 = 'A';

The index yourTable (c1, c2) will improve.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement