CODEWITHSUNDEEP
sqlsql-server
user3344443
user3344443

Reputation: 475

SQL using subquery table multiple times

I want to pull data from table t

 select 
 (select round(avg(High),2) from t where  High!=0)as High,
 (select round(avg(Med),2) from t where Med!=0) as Med,
  (select round(avg(Low),2) from t where  Low!=0) as Low

and my table t is

    select AAAA as High, BBBB as Med, CCCC as Low from TABLE1 where company like '%3m%'
  union all
  select  
  (select round(XXX,2) from TABLE2 where company like '%3m%' and XXX!=0)as High,
 (select round(YYY),2) from TABLE2 where company like '%3m%' and YYY!=0) as Med,
 (select round(ZZZ,2) from TABLE2 where company like '%3m%' and ZZZ!=0) as Low

i wonder how i can use a query to pull data from table t without using subquery repetitively (originally I don't have t)? what I'm really trying to do is to get average from two rows where one row is not zero Thanks for any advice!

Upvotes: 0

Views: 152

Answers (3)

Guillermo Gutiérrez
Guillermo Gutiérrez

Reputation: 17789

Of course you can:

select
    round(SUM(case when High<>0 then High else 0 end)/SUM(case when High<>0 then 1 else 0 end),2) as High,
    round(SUM(case when Med<>0 then Med else 0 end)/SUM(case when Med<>0 then 1 else 0 end),2) as Med,
    round(SUM(case when Low<>0 then Low else 0 end)/SUM(case when Low<>0 then 1 else 0 end),2) as Low
from
  (
    select AAAA as High, BBBB as Med, CCCC as Low from TABLE1 where company like '%3m%'
    union all
    select round(XXX,2) High, round(YYY,2) Med, round(ZZZ,2) Low from TABLE2 where company like '%3m%'
  )

Upvotes: 0

Joseph B
Joseph B

Reputation: 5669

Try this query. It uses the CASE function. If the condition is not satisfied, then the value is assumed to be NULL, so that it would not be counted in the average.

Since you do not have t to start with, you can create it using a CTE.

    WITH t as
    (
     select AAAA as High, BBBB as Med, CCCC as Low from TABLE1 where company like '%3m%'
      union all
      select  
      (select round(XXX,2) from TABLE2 where company like '%3m%' and XXX!=0)as High,
     (select round(YYY),2) from TABLE2 where company like '%3m%' and YYY!=0) as Med,
     (select round(ZZZ,2) from TABLE2 where company like '%3m%' and ZZZ!=0) as Low
    )
SELECT 
ROUND(AVG(case when company like '%3m%'and ABC <> 0 then High else null end), 2) High,
ROUND(AVG(case when company like '%3m%'and DEF <> 0 then Medium else null end), 2) Medium,
ROUND(AVG(case when company like '%3m%'and GHI <> 0 then Low else null end), 2) Low
from t;

Upvotes: 1

TomTom
TomTom

Reputation: 62093

You can not because every of the subqueries is different -in the filter condition.

Upvotes: 0

Related Questions