Link to home
Start Free TrialLog in
Avatar of Michael Paravicini
Michael ParaviciniFlag for Chile

asked on

Avg in SQL without including all records

Hi, I need a query which computes different rates averages... However, not sure how to do it as the avg will include the omitted records. I have the following query to date:

SELECT Format([checkin],"yyyy mm") AS YearMonth,  Format(Avg(IIF(PCKGID<>1,0,Resvalue/TotNights)),"$#,##0.00") As AllInclPerPax, Format(Avg(IIF(PCKGID<>2,0,Resvalue/TotNights)),"$#,##0.00") As BBPerPax
FROM QueryFull
WHERE cancel=False
GROUP BY Format([checkin],"yyyy mm");

The result is not correct as the avg will include the omitted records. Any help is very much appreciated! Thanks Michael
Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)
Flag of United States of America image

I'm not sure I understand your problem.

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

User generated image
Here is another example:

 User generated image
Avatar of Sharath S
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

Open 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)

Open 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
Avatar of Michael Paravicini


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
Avatar of yo_bee
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In your example, you need to calculate average by grouping similar items.

SELECT RateType, AVG(ResValue) AS Avg_ResValue
  FROM your_table

Open 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

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.