New to SQL, is not null-average

[Smack]

Hey folks. I am working with a VERY simple form/subform for an ongoing customer survey. There are 3 categories with 12 questions each. Only one question from each category will be asked when a customer places an order. So if customer A places 12 orders in one month, it would take one month to complete that particular record. Customer B may take six months. Each response asks for a numerical 1-5 value.
I need to able to show a running average per customer record. Problem is that for the 12 questions, only 5 may have been answered, so I need to show only not null entries, and then average those. I have never used SQL, and am trying to figure it out.So far I have the following,
SELECT [Delivery Performance], [Product Quality], [Product Cost], [Ease of Doing Business], [Claims Processing], [Customer Service], [Packaging Quality], [Invoicing], [Order Processing], [Product Development], [Sample Submission], [Pricing]
FROM sub_t
WHERE Is Not Null;
I am getting a syntax/return error for the not null on what little I have so far, and still don't know how to get the average.
Any help would be appreciated.

 

[IFELSETHEN]

Howdy .....
Your problem is 'WHERE Is Not Null;'
You need to have a field name in place of the 'Is', like this 'WHERE [Sample Submission] = not null'.

John


*********************
John Nyhart

http://www.nyhart.com

*********************

 

[r937]

john, WHERE foo IS NOT NULL, not WHERE foo = NOT NULL

as far as screening out the nulls is concerned, aggregate functions already do that, smack, if you were thinking of using SUM and COUNT to calculate your averages


rudy

 

[IFELSETHEN]

OOPS ..... sorry abt that ..... John

*********************
John Nyhart

http://www.nyhart.com

*********************

 

[Smack]

"if you were thinking of using SUM and COUNT to calculate your averages"

And this would be done in SQL, or just a select query?

 

[r937]

yes, it would be done in sql, in a select query

can you please explain what you mean by "a running average per customer record"

best would be to show some sample rows and then show the result you expect to get

 

[Smack]

Customer Performance Quality Cost Avg
Cust A 5 5 5
Cust B 3 3 4 3.3

 

[r937]

select Customer
, Performance
, Quality
, Cost
, iif(isnull(Performance),0,Performance) +
iif(isnull(Quality),0,Quality) +
iif(isnull(Cost),0,Cost) /
( iif(isnull(Performance),0,1) +
iif(isnull(Quality),0,1) +
iif(isnull(Cost),0,1) ) as Avg

 

[Smack]

Thanks, I'll give it a try.

 

[r937]

oops, the numerator of the division also needs parentheses!

... , (
iif(isnull(Performance),0,Performance) +
iif(isnull(Quality),0,Quality) +
iif(isnull(Cost),0,Cost)
) /
(
iif(isnull(Performance),0,1) +
iif(isnull(Quality),0,1) +
iif(isnull(Cost),0,1)
) as Avg

 

[Smack]

Okay, I know i am bordering on pathetic here, but another question with this. When I gave the fieldname example, I shortened it to give an idea of what I was doing. The book I have says field names with spaces must be bracketed. I'm not sure how this would work for the items you have in parenthesis,if you could show me on the last line, I'll redo all the other you posted.
select
, [Delivery Performance]
, [Product Quality]
, [Product Cost]
, [Ease of Doing Business]
, [Claims Processing]
, [Customer Service]
, [Packaging Quality]
, Invoicing
, [Order Processing]
, [Product Development]
, [Sample Submission]
, Pricing
, iif(isnull(Performance),0,Performance) +
A GREAT BIG THANK YOU FOR YOUR HELP!!!

 

[r937]

... , (
iif(isnull([Performance]),0,[Performance]) +
iif(isnull([Quality]),0,[Quality]) +
iif(isnull([Cost]),0,[Cost])
) /
(
iif(isnull([Performance]),0,1) +
iif(isnull([Quality]),0,1) +
iif(isnull([Cost]),0,1)
) as Avg

 

[Smack]

r937,
I was sidetracked with ISO for awhile and just got back to this, below is an error mesage based on what I have so far,

The select statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect.

SELECT
, [Delivery Performance]
, [Product Quality]
, [Product Cost]
, [Ease of Doing Business]
, [Claims Processing]
, [Customer Service]
, [Packaging Quality]
, Invoicing
, [Order Processing]
, [Product Development]
, [Sample Submission]
, Pricing
, (
iif(isnull([Delivery Performance]),0, [Delivery Performance]) +
iif(isnull([Product Quality]),0,[Product Quality]) +
iif(isnull([Product Cost]),0, [Product Cost]) +
iif(isnull([Ease of Doing Business]),0,[ Ease of Doing Business]) +
iif(isnull([Claims Processing]),0, [Claims Processing]) +
iif(isnull([Customer Service]),0, [Customer Service]) +
iif(isnull([Packaging Quality]),0, [Packaging Quality]) +
iif(isnull([Invoicing]),0, [Invoicing]) +
iif(isnull([Order Processing]),0, [Order Processing]) +
iif(isnull([Product Development]),0, [Product Development]) +
iif(isnull([Sample Submission]),0, [Sample Submission]) +
iif(isnull([Pricing]),0, [Pricing])
) /
(
iif(isnull([Delivery Performance]),0,1) +
iif(isnull([Product Quality]),0,1) +
iif(isnull([Product Cost]),0,1) +
iif(isnull([Ease of Doing Business]),0,1) +
iif(isnull([Claims Processing]),0,1) +
iif(isnull([Customer Service]),0,1) +
iif(isnull([Packaging Quality]),0,1) +
iif(isnull([Invoicing]),0,1) +
iif(isnull([Order Processing]),0,1) +
iif(isnull([Product Development]),0,1) +
iif(isnull([Sample Submission]),0,1) +
iif(isnull([Pricing]),0,,1)
) as Avg

 

[r937]

don't you need a GROUP BY somewhere along the line?

i mean, you cannot calculate an average on each individual row, so the only point in doing the average calculation is to do it over a series of rows

what constitutes the group for which an average is required?

rudy

 

[Smack]

All 12 fields. Like I stated earlier, I am clueless on SQL, sorry.

 

[lespaul]

Where's your from clause?

FROM SURVEYTABLE GROUP BY CUSTOMER



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[r937]

sorry, i just re-read the thread

GROUP BY is not required, these appear to be averages "across the rows"

the only word that looks like a reserved word is "Avg"

try misspelling it, e.g. Averidge


rudy

 

[Smack]

Rudy, tried what you said with the following (except I tried averidge and Avg) and got the same message.
SELECT
FROM sub_t;
, [Delivery Performance]
, [Product Quality]
, [Product Cost]
, [Ease of Doing Business]
, [Claims Processing]
, [Customer Service]
, [Packaging Quality]
, Invoicing
, [Order Processing]
, [Product Development]
, [Sample Submission]
, Pricing
, (
iif(isnull([Delivery Performance]),0, [Delivery Performance]) +
iif(isnull([Product Quality]),0,[Product Quality]) +
iif(isnull([Product Cost]),0, [Product Cost]) +
iif(isnull([Ease of Doing Business]),0,[ Ease of Doing Business]) +
iif(isnull([Claims Processing]),0, [Claims Processing]) +
iif(isnull([Customer Service]),0, [Customer Service]) +
iif(isnull([Packaging Quality]),0, [Packaging Quality]) +
iif(isnull([Invoicing]),0, [Invoicing]) +
iif(isnull([Order Processing]),0, [Order Processing]) +
iif(isnull([Product Development]),0, [Product Development]) +
iif(isnull([Sample Submission]),0, [Sample Submission]) +
iif(isnull([Pricing]),0, [Pricing])
) /
(
iif(isnull([Delivery Performance]),0,1) +
iif(isnull([Product Quality]),0,1) +
iif(isnull([Product Cost]),0,1) +
iif(isnull([Ease of Doing Business]),0,1) +
iif(isnull([Claims Processing]),0,1) +
iif(isnull([Customer Service]),0,1) +
iif(isnull([Packaging Quality]),0,1) +
iif(isnull([Invoicing]),0,1) +
iif(isnull([Order Processing]),0,1) +
iif(isnull([Product Development]),0,1) +
iif(isnull([Sample Submission]),0,1) +
iif(isnull([Pricing]),0,,1)
) as Avg

 

[r937]

move the FROM clause --

select foo
, bar
, lotsastuff
, ...
from tablename
where whatever...