We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Agregate function over many columns

mock5c
mock5c asked
on
Medium Priority
260 Views
Last Modified: 2012-06-21
I've provided part of a CSV file.  I would like a single query that can show me averages and stdev for PartNums for the length,weight, and height columns.

e.g.

avg(length),avg(weight),avg(height) of:
PartID=A,PartNum=P1;PartID=B,PartNum=P1;PartID=C,PartNum=P1
A,P2;B,P2;C,P2
A,P3;B,P3;C,P3
and so on ...

I have over 70 columns so eventually I'd like to apply this query for all columns.


PartID,PartNum,PartLength,PartWeight,PartHeight
A,P1,8.0353,0.551,210.0
A,P2,6.7334,0.478,179.5
A,P3,11.9139,0.922,125.1
A,P4,3.8513,0.291,99.9
A,P5,2.6203,0.199,80.7
B,P1,14.8999,1.239,35.6
B,P2,26.7577,3.513,69.8
B,P3,10.2892,1.000,112.0
B,P4,9.7443,0.872,103.8
B,P5,0.1506,0.013,30.5
C,P1,2.8813,0.178,35.6
C,P2,18.519,2.001,231.8
C,P3,7.0322,0.899,77.7
C,P4,5.5400,0.313,61.2
C,P5,3.0951,0.190,35.9

Open in new window

Comment
Watch Question

see this link:
           http://support.microsoft.com/kb/209839
Although Microsoft Access has several built-in functions that enable you to perform statistical analysis across records, it does not have a built-in function to perform statistical analysis across multiple columns within a single row. This article shows you how to create several sample user-defined functions that you can use to get row-level statistics in Access.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
I'm not sure which average you want: PartNums across PartIDs or PartIDs across PartNums... Since you have unique combinations, you clearly don't want one record for each. Have you tried a simple total query, something like:

SELECT
 PartNum,
 Avg(PartLength) AS AvgOfPartLength,
 StDev(PartLength) AS StDevOfPartLength,
 Avg(PartWeight) AS AvgOfPartWeight,
 StDev(PartWeight) AS StDevOfPartWeight,
 Avg(PartHeight) AS AvgOfPartHeight,
 StDev(PartHeight) AS StDevOfPartHeight
FROM YourCSV
GROUP BY PartNum;

(°v°)

Author

Commented:
That link seems useful but I don't need to compute statistical analysis over multiple columns.  I only need to compute on a per column basis.  So, for example, we can forget about my weight and height columns and just pretend there is only a length column.  Now, from the length column, I would like to know:

Average of
PartID=A,PartNum=P1;PartID=B,PartNum=P1;PartID=C,PartNum=P1
A,P2;B,P2;C,P2
A,P3;B,P3;C,P3
and so on ...

That is:
avg(8.0353,14.8999,2.8813)
avg(6.7334,26.7577,18.519)
avg(11.9139,10.2892,7.0322)
and so on ...


Commented:
The query I showed does just that. Import or link your CSV, open the query assistant, choose "total query", group by PartNum, and apply the functions you need to each column (you can use the same column name several times to get average and standard deviation for example).

Good luck!
(°v°)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.