Query With Multiple Subqueries Efficiency?

I have data that looks like this:

State   Sex  ----    ---  GA      M  GA      M  GA      F  GA      F  GA      F  NY      M  NY      M  NY      M  NY      M  NY      F  NY      F  NY      F  NY      F  NY      F  

I want the result to be:

1. one row per state
2. col1 State
3. col2 count of Males
4. col3 count of Females
5. col4 total count by state
6. col 5 percent Male by state

The query I am using is:

select t.state State,   M.count Male,   F.count Female,   count(t.state) Total,  CONCAT(ROUND(CAST(M.count as float)/CAST(count(t.state) as float)*100, 2), '%') as calc  from MyTable t  join  (    select state, count(sex) as count     from MyTable where sex ='M'     group by state) M     on t.state = M.state   join (    select state, count(sex) as count     from MyTable where sex ='F'     group by state) F     ON M.state = F.state    group by t.state, m.count, F.count;  

The above query works but I am wondering if I did this in the most effecent way. This was done using SQLServer but I think this should be the same for all RDBMS. The link is here: http://sqlfiddle.com/#!18/7a969/87

https://stackoverflow.com/questions/66739812/query-with-multiple-subqueries-efficiency March 22, 2021 at 11:04AM