1. Registered User
Join Date
Apr 2003
Posts
43

I have 2 question.
I want to know the percentage for a set of records where X>=-65.
Everything i write an expression i get an error

percent(case when V >= -65 then 1 else 0 end) V_lt_eq_65

Second question, How do I write if I have 2 records similar, I want to average out the value corresponding to those records

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
This should work,

select 100 * Valid/Total
from (select sum(case when V >= -65 then 1 else 0 end) as Valid, count(*) as Total from YourTable) Subquery

...and this might fly too:

select 100 * sum(case when V >= -65 then 1 else 0 end)/count(*) from your table

blindman

3. Registered User
Join Date
Apr 2003
Posts
43

## Re: SQL question

tHANKS
that helps
my second question
I have a table which has redundant values..I have X and Y vales that are same and there are some corrsponding z values
Example
x y z
1 2 3.4
1 2 3.8
I need to average out Z value for all the duplicates of x and Y
can some one help
thanks

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Come on, namitao, this one is easy!

select x, y, Avg(z) as AvgZ
from ourTable

Spend some time with Books Online!

blindman

5. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
Almost. Actually, it's

select x, y, Avg(z) as AvgZ
from ourTable
GROUP BY x, y

but I agree with blindman, that this is really basic SQL.

6. Registered User
Join Date
Apr 2003
Posts
43
Dude..
Possibly I havent put the question..rite..if it was simple..i wouldnt waste ur valuabe time...let me try putting it forward again.
My table has columns A, b,c, d, e, f. Now A and B have the same numbers but they have diffrent c,d,e,f. I want to basically average 'c' out the duplicates for the duplicates of a and b. where a and b are same. But the point is d e f are also related.
a b c d e f
1 2 3.4 / * -
1 2 5.6 + / *
1 2 8.3 * / +
3 4 2.3 - + /
5 6 7.2 / * /
thus the ans must be
a b c d e f
1 2 5.8 ? ? ?
3 4 2.3 - + /
5 6 7.2 / * /

My query looks like
Select avg(A.c),A.a A. b
from temp_table1 A, temp_table1 B
where A.d <> B.d
group by Aa, A.b

hope this is understanbable
eles thanks for the help

7. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
So, when A and B are a unique combination, you want the values of e and f, otherwise a '?' ? Try this!

Select a, b, avg(c), case count(*) = 1 then max(d) else '?' end, case count(*) =1 then max(e) else '?' end, case count(*) =1 then max(f) else '?' end
from temp_table1
group by a, b

8. Registered User
Join Date
Apr 2003
Posts
43
Originally posted by DoktorBlue
So, when A and B are a unique combination, you want the values of e and f, otherwise a '?' ? Try this!

Select a, b, avg(c), case count(*) = 1 then max(d) else '?' end, case count(*) =1 then max(e) else '?' end, case count(*) =1 then max(f) else '?' end
from temp_table1
group by a, b

So, when A and B are a unique combination, I want the values of c,d,e and f as they are
But if a and b are not unique( are duplicates) i want to average out c and also want values of d and e which i am not sure how to get them..
thanks a ton for the help
Namita

9. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
Originally posted by namitao