Results 1 to 9 of 9

Thread: SQL question

  1. #1
    Join Date
    Apr 2003
    Posts
    43

    Unanswered: SQL question

    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

    Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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. #3
    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. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Come on, namitao, this one is easy!

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

    Spend some time with Books Online!

    blindman

  5. #5
    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    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.
    thus i had earlier put
    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. #7
    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
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    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

    Hey that was helpful...
    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. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by namitao
    Hey that was helpful...
    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
    When the combination of a and b isn't unique, you have several c, d, e, and f. For c, you choose the average. For d, e, and f, you have a choice between Min(), Max(), Count(DISTINCT ), and some exotic statistical functions. If your d, e, and/or f and summarizable, you may also consider to use sum()
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •