trjhtr

I currently have 6 very similar queries that I'm trying to increase performance on by making them all subqueries in 1 query. I'm not sure what is better performance wise, keeping them 6 separate queries, 1 query with 6 subqueries (as shown below), or some other method that I'm unfamiliar with.

SELECT 
 (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName) AS total,
 (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_A BETWEEN 100 AND 199) AS code_a_low_count,
 (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_A BETWEEN 200 AND 299) AS code_a_high_count,
 (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_B BETWEEN 100 AND 199) AS code_b_low_count,
 (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_B BETWEEN 200 AND 299) AS code_b_high_count,
 (SELECT COUNT(*) FROM table_one WHERE date=myDate AND name=myName AND CODE_C BETWEEN 100 AND 199) AS code_c_count
FROM table_one
WHERE ROWNUM=1

I can suggest you use common table expression (Oracle calls it "Subquery Factoring") at the very least you will be doing the filtering by date and name one time only

WITH filter_by_date_name AS ( 
 SELECT CODE_A, CODE_B, CODE_C FROM table_one WHERE date=myDate AND name=myName
)
SELECT 
 (SELECT COUNT(*) FROM filter_by_date_name) AS total,
 (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_A BETWEEN 100 AND 199) AS code_a_low_count,
 (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_A BETWEEN 200 AND 299) AS code_a_high_count,
 (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_B BETWEEN 100 AND 199) AS code_b_low_count,
 (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_B BETWEEN 200 AND 299) AS code_b_high_count,
 (SELECT COUNT(*) FROM filter_by_date_name WHERE CODE_C BETWEEN 100 AND 199) AS code_c_count
FROM filter_by_date_name
WHERE ROWNUM=1

This is a simple task for conditional aggregation using CASEs:

SELECT 
 COUNT(*) AS total,
 COUNT(CASE WHEN CODE_A BETWEEN 100 AND 199 THEN 1 END) AS code_a_low_count,
 COUNT(CASE WHEN CODE_A BETWEEN 200 AND 299 THEN 1 END) AS code_a_high_count,
 COUNT(CASE WHEN CODE_B BETWEEN 100 AND 199 THEN 1 END) AS code_b_low_count,
 COUNT(CASE WHEN CODE_B BETWEEN 200 AND 299 THEN 1 END) AS code_b_high_count,
 COUNT(CASE WHEN CODE_C BETWEEN 100 AND 199 THEN 1 END) AS code_c_count
FROM table_one
WHERE date=myDate AND name=myName

As you're COUNTing you can return anything (besides NULL) in THEN.

This can also be written as a SUM over 1/0:

SUM(CASE WHEN CODE_A BETWEEN 100 AND 199 THEN 1 ELSE 0 END)