Hi

Select AVG(tax) from invoice where type='sales tax'

even though there are records in the invoice table why does the above query return null

Your query is correct... May be the following factors will affect

1. May be you don't have a vlaue 'sales tax' in type column (is there any space between sales and tax in your type column ...check it)

2. May be you have the value in type column like 'Sales Tax'

3. May be you have the value in type column like 'SALES TAX'

--------------------
suresh

ya i agree with suresh, there might only be mismatch in the value that u r giving in single quotes. in SQL what ever u give in single quotes become case sensitive.

[QUOTE=bbkcdb;8784]Select AVG(tax) from invoice where type='sales tax'

even though there are records in the invoice table why does the above query return null[/QUOTE]

Dear you have written where type is 'sales tax',

Is it possible?

Or it should be like sales_tax

or SalesTax.

plz verify.

Hi Brijesh

thats possible
bcoz 'sales tax' is a value thats stored in a column, and we can store values with spaces :)

Regards
Risham

oh yes,
I got it.

then what can be the problem?

is avg function works on a particular value?

As i think we run it on a column of a table!

correct me if i m wrong...

nothing is wrong with the query,

as already posted in the thread, anything that u giv in single quotes becomes case sensitive. may me that data stored in column is SALES TAX or Sales Tax or any other combination of upper n lower cases of sales tax. or even it can be salestax. we need to check out n giv the proper value.

Regards
Risham

Check the case of the sale tax you have given in the condition.

Which of the following is true of the interpreter?

_____ It translates instructions written in assembly language into machine language.
or

_____ It translates machine language into a high-level language.

or
_____ It translates a high-level language into machine language

if the column has varchar or char type you must have to give in the ' '.otherwise it will errored out.

The query appears to be right mate. But try adding an and clause in the query to pick non-null columns in the table.

Select AVG(tax) from invoice where type='sales tax' and tax !=NULL

It should resolve the issue.

Cheers!
Kalayama

hey kalayama,

watever i m getting,acc. to you if this tax column is null then it will not give the required answer.i m right?

if yes.then i have checked with one table having null and not null values and i m taking avg of that colum with same type of condition and i m getting the right answer!

reeta