A simple SQL weaknesses with operator avg()

sqlavg

Advertisements

2 comments so far

  1. Guy Hagen on

    Hmm, you are absolutely correct. This throws an unpleasant doubt on all of the statistics I have provided to clients based on SQL queries over the years. Have you checked whether or not this is a listed bug on the MySQL developers list?

  2. Lennart Jonsson on

    Yes, it is interesting to see how nulls destroy all kinds of mathematical properties. Consider for example:

    with T(a,b) as (
    values (null,null),(1,null),(null,1),(1,1)
    )
    select sum(a)+sum(b) as sum_a_plus_sum_b,
    sum(a+b) as sum_a_plus_b
    from T

    SUM_A_PLUS_SUM_B SUM_A_PLUS_B
    —————- ————
    4 2

    I.e. sum(a) + sum(b) sum(a+b)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: