SQL

gardoyle27

could sombody help me with this,

SELECT AVG(advance), AVG(price)
FROM titles
GROUP BY pub_id, category
What do i need to add to remove the NULL values?
many thanks
Gareth

gardoyle27

Cancel that, just twigged it, i'm a numpty at times!!

Jagera

Just out of interest, how are you using the results of that query?

Normally, one would include the pub_id and category, so you know what average you are looking at, i.e.

SELECT pub_id, category, AVG(advance), AVG(price)
FROM titles
GROUP BY pub_id, category

gardoyle27

i'm not using it for anything, i had 100 sql questions to do for college and that was one of the questions,

Jagera

What did you put for your answer? I'm a bit stumped, as it could be mis-interpreted.

1. remove nulls from result set?
2. remove nulls from calculating the averages (I know how SQL handles these internally)

gardoyle27

Jagera wrote: »

What did you put for your answer? I'm a bit stumped, as it could be mis-interpreted.

1. remove nulls from result set?
2. remove nulls from calculating the averages (I know how SQL handles these internally)

it was to remove the nulls from the result set
can remeber exactly now but it was something like this

SELECT AVG(advance), AVG(price)
FROM titles
WHERE advance IS NOT NULL
OR price IS NOT NULL
GROUP BY pub_id, category