CODEWITHSUNDEEP
sqlaverageconditional-aggregation
Hussain Mahdi Farhanian
Hussain Mahdi Farhanian

Reputation: 13

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

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.

Upvotes: 1

Views: 34

Answers (1)

Charlieface
Charlieface

Reputation: 71579

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.

Upvotes: 0

Related Questions