Click here to Skip to main content
15,072,433 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to get average for multiple percentage values in sql query

my query results are

94.58
96

and my query is:

SQL
select Sum(Case when (b.test1 <> b.test2
      OR (b.test1 IS NULL AND b.test2 IS NOT NULL)
      OR (b.test1 IS NOT NULL AND b.test2 IS NULL)) then 1 else 0 end) as sample from table b
<pre lang="sql">
      union

select((Sum(a.counterid)*1.0/Sum(a.fieldcount))*100)as sample2 from table2 a



Please help me on this.
Posted
Updated 7-Apr-15 0:15am
v5
Comments
Maciej Los 7-Apr-15 7:03am
   
Help what? What's input?

Hi,

Check this...

SQL
SELECT avg(sample) from
(
select Sum(Case when (b.test1 <> b.test2
      OR (b.test1 IS NULL AND b.test2 IS NOT NULL)
      OR (b.test1 IS NOT NULL AND b.test2 IS NULL)) then 1 else 0 end) as sample from table b
      union
select((Sum(a.counterid)*1.0/Sum(a.fieldcount))*100)as sample2 from table2 a
) x


Hope this will help you.

Cheers
   
Comments
SukirtiShetty 8-Apr-15 0:20am
   
I am getting correct answer but why did you used x in the query. Please explain the query.
Magic Wonder 8-Apr-15 2:39am
   
Here x is alias/temp name given to the set of tabular format data, from which you can fetch data as per your choice.
SukirtiShetty 8-Apr-15 2:42am
   
ok thank you.
Magic Wonder 8-Apr-15 3:21am
   
Your welcome.
Using Common table expressions
SQL
WITH T(I) AS 
(
    select Sum(Case when (b.test1 <> b.test2
          OR (b.test1 IS NULL AND b.test2 IS NOT NULL)
          OR (b.test1 IS NOT NULL AND b.test2 IS NULL)) then 1 else 0 end) as sample from table b
    UNION
    select((Sum(a.counterid)*1.0/Sum(a.fieldcount))*100)as sample2 from table2 a
}
SELECT AVG(I) FROM T
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900