Re: Query for Averageing
From: MGFoster (me_at_privacy.com)
Date: 04/06/04
- Next message: Allen Browne: "Re: Event procedure -Query"
- Previous message: MGFoster: "Re: Non Equi Join"
- In reply to: RobinF: "Query for Averageing"
- Next in thread: TracyL: "Re: Query for Averageing"
- Reply: TracyL: "Re: Query for Averageing"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 06 Apr 2004 01:05:36 GMT
RobinF wrote:
> Can someone help me set up a query where the totals of
> the fields are added up and then divided by the number of
> fields to obtain an average.
>
> However, if there is no number in a field then the
> average is to not include that field in the averge.
>
> e.g. Field1 Field2 Field3 Field4 Avg
> 10 8 12 25 13.75 = Total / 4
>
> 10 25 8.75 = Total / 4
>
> What I want Is not Total / 4 but Total / no of field with
> numbers So I Need Total / 2 becouse only 2 fields have
> numbers.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
If this is a table it is incorrectly designed. If the table was
designed correctly you could use the SQL function Avg() to get the
correct average. E.g.:
Table Design:
ID The record id - no nulls
NumberField Your number field - can be null
Get the average:
SELECT ID, Avg(NumberField)
FROM TableName
GROUP BY ID
Using your data as the example you'd get the resultset of:
ID NumberFieldAvg Raw Data
------- -------------- --------
1 13.75 (10+8+12+25)/4
2 17.5 (10+25)/2
-- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQHICLIechKqOuFEgEQL5jACgschsevDbfN1YFODc/uOZOIrVnj8AoJ3L ocd1sKukYEM/BE9bYarrYy5t =lTky -----END PGP SIGNATURE-----
- Next message: Allen Browne: "Re: Event procedure -Query"
- Previous message: MGFoster: "Re: Non Equi Join"
- In reply to: RobinF: "Query for Averageing"
- Next in thread: TracyL: "Re: Query for Averageing"
- Reply: TracyL: "Re: Query for Averageing"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
