Working with Null Values

[Chris]

Any suggestions on how to accomplish a few simple formulas would be helpful.
The information I have is 52 weeks worth of orders by item, by customer. I
am trying to calcluate the standard deviation and average for each item.
When a customer has no order, it shows up as a null value, which is not good
according the formula. Should i convert these null values to zero? If i go
this route, can the standard deviation formula be written that would exclude
the now zero values, as that would skew the data since i am trying to find
values on the quantity of each order made by a customer.

 

[Michel Walsh]

Keep them as NULL since NULLs are excluded from aggregates: a record with a
null value won't increase the COUNT, while, as you pointed out, a zero would
increase the 'N' value, but not the SUM.

You can exclude values BEFORE making aggregate through a WHERE clause:


SELECT SUM(x)
FROM somewhere
WHERE x>0
GROUP BY y
HAVING COUNT(x) >5



As example, sum strictly positive values, and keep the groups (given by the
value in the y field) that have at least 5 candidates in their group (as to
eliminate groups having too few records to provide a significant stat... It
is just for an illustration purpose of using HAVING, which operates AFTER
the WHERE clause, while the WHERE clause operates BEFORE the
grouping/aggregation process).


Vanderghast, Access MVP.

 

[Chris]

That is very helpful, thanks. Although, I think I would benefit from another
question answered: My data has weeks extending out into 52 columns. Is it
possible to take a standard deviation in Access with the data in this format?
Do I have to make an append query to keep all of the week's worth of data in
the same column or can the standard deviation f(x) be written to include a
range in column form like in Excel?

 

[Michel Walsh]

No, in SQL, you cannot automatically aggregate through columns... On the
other hand, you can 'normalize' your data:


SELECT [1] As data, 1 AS weekNumber FROM mydata

UNION ALL

SELECT [2], 2 FROM myData

UNION ALL

SELECT [3], 3 FROM myData
....
....
....
UNION ALL

SELECT [52], 52 FROM myData




assuming your 52 columns have the name [1] to [52], that big union all
query will present your data on 2 columns: (data, week_number)

You can then make:


SELECT AVG(data)
FROM unionQuery
GROUP BY weekNumber



Sure, I would make a table from the big union all query, and I would define
an index on the second field, if you have more than a couple of thousand of
rows in the union all query result.





Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

ah, and be careful with weeknumber 1 and week number 52 or 53: they may
represent incomplete week (weeks with just a few day, not all seven days)
and their aggregate may be a poor representative of a standard week: for
some stats, that MAY be a problem. (A solution is to eventually combine week
1 with week 53 of the previous year, to get the 7 days in that "week with
two names")


Vanderghast, Access MVP