I need to get the average of 2 SQL query results in ASP.NET C#

Question

I am trying to fetch some data using the count function in sql into my asp.net project in 3 parts:

Part one will use the 1st SQL query as follows:

select distict, shop_number, count(Emp_id) 
from Shops_and_Employee_Table 
group by distict, shop_number

Part two will use the 2nd SQL query:

select distict, shop_number, count(Emp_id) 
from Shops_and_Employee_Table 
where score >= 80 
group by distict, shop_number 

I am showing the above results in 2 different GridViews in my project.

Now I need to show a new GridView to get the average of the second query compared to the first one, I used the following but didn't get the correct result:

select 
    a.distict, a.shop_number, b.number / a.number
from 
    (select distict, shop_number, count(Emp_id) as number 
     from Shops_and_Employee_Table 
     group by distict, shop_number) a
join 
    (select distict, shop_number, count(Emp_id) as number 
     from Shops_and_Employee_Table 
     where score >= 80 
     group by distict, shop_number) b on a.shop_number = b.shop_number

Can someone tell me what did I write in my average query? Or suggest if I should average the numbers in C# code behind it self but explain how since I don't know how to apply it.

Waiting for your great help.

I tried to do it in SQL itself but I got wrong numbers, someone told me to do it better in C# code behind but I couldn't, please help.

Answer 1

You can use conditional aggregation to combine the two queries.

select
  se.distict,
  se.shop_number,
  count(*) as TotalCount,
  count(case when se.score >= 80 then 1 end) as Over80Count,
  count(case when se.score >= 80 then 1 end) * 1.0 / count(*) as Percent
from Shops_and_Employee_Table se
group by
  se.distict,
  se.shop_number;

In Postgres and some other databases you can use the FILTER syntax instead.

---

Your original code would have worked, if rather inefficiently, if you would have joined also by distict, and used a left join, and converted to decimal by multiplying by 1.0.