Avg in SQL without including all records

I'm not sure I understand your problem.

Omitted records will not be included in your average.  Here is a simple test:

Here is another example:

 

How do you know if a record is omitted? Do you have a field to represent that? If so, can you include that in the WHERE clause.

WHERE omitted_field <> 'omitted'

@Sharath - from the original post:

WHERE cancel=False

Select allOpen in new window

Are you trying to do something like this?

 select year(Transdate)as [year],month(Transdate)as [month],
		avg(Case when stage <> 'billed' then value else null end) as NotBilled,
		avg(Case when stage <> 'wip' then value else null end) as NotWIP
		from Transactions
		where year(transdate) =   '2016'
Group by year(Transdate),month(Transdate)

Select allOpen in new window

Please disregard the lack of the formatting
.

year        month       NotBilled                               NotWIP
----------- ----------- --------------------------------------- ---------------------------------------
2016        1           14.955020                               492.523185
2016        2           32.510240                               412.929915
2016        3           56.734543                               542.809811
2016        4           111.553329                              461.521153
2016        5           66.373036                               727.830537
2016        6           96.414381                               461.795339
2016        7           394.863501                              233.488841
2016        8           297.647818                              80.708655
2016        9           2273.720000                             NULL
2016        12          738.090000                              NULL

If I have the following table in Access:
RateType      ResValue
1                      $1,000.00
1                     $2,000.00
1                      $3,000.00
2                     $5,000.00
2                     $10,000.00
2                    $15,000.00

Now in a single Select I would like to compute the AVG for all RateType=1 (which is USD 2000) and the average  for all RateType=2 (which is USD 10.000). However, if I use

SELECT avg(IIf(ratetype=1,resvalue,0)), Avg(Iif(ratetype=2,resvalue,0))
FROM TestTable;

then the result is wrong as it computes the average using all 6 rows. I very much appreciate any help..
Cheers Michael

In your example, you need to calculate average by grouping similar items.

SELECT RateType, AVG(ResValue) AS Avg_ResValue
  FROM your_table
GROUP BY RateType

Select allOpen in new window

Is this a Homework assignment.
It sure seems like one.

No  - it is the same project I'm working on as always... pls don't ask me this qustion again.... :)

NP ;)

What is your over goal here?
Does the look of it make a difference or can you use what Sharath suggested?


Do no need it to look like

Rate1     Rate2
--------      --------
$2000    $10000

Thanks for asking - the solution from Sharath is perfect! I will close in a minute...

Perfect ... Thank you Michael

@MPIM

You awarded me all the points, but mentioned that Sharath suggestion was perfect.
I would request to have this question reopened so you can award the point accordingly.

At least a 50/50 split.