CODEWITHSUNDEEP
sqloracle-database
S.K
S.K

Reputation: 3

how to reducible this SQL query

I have two tables, class_students and school_students. I need to count the total number of schools and the proportion of the whole class, but I used two identical queries. This is my query:

SELECT t.class_name,
       (SELECT COUNT (1) FROM school_students) as total_school_population,
       COUNT (1) / (SELECT COUNT (1) FROM school_students)
FROM class_students t;

so, how do I optimize it?

Upvotes: 0

Views: 90

Answers (2)

Nimit Rastogi
Nimit Rastogi

Reputation: 51

To avoid the recalculation of total school count for each record, save the value in a column user variable

COLUMN school_count NEW_VALUE school_count
SELECT count(*) school_count
  FROM school_students;

Then use this variable for the division expression

SELECT class_name,
       '&school_count' as total_school_population,
       COUNT (1) / &school_count as class_proportion 
  FROM class_students
 GROUP BY class_name;

In case the tables contain very large number of records, do gather statistics and/or use optimizer hints like /*+ ALL_ROWS*/

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

If those two table doesn't have any relationship, you can try to use to CROSS JOIN let subquery get result set then use the column.

SELECT t.class_name, 
       t1.cnt total_school_population, 
       COUNT(1)/ t1.cnt
FROM class_students t CROSS JOIN 
(
    SELECT COUNT(1) cnt
    from school_students
) t1
group by t.class_name,t1.cnt

Upvotes: 3

Related Questions