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

on
Medium Priority
260 Views
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
Comment
Watch Question

## View Solutions Only

Commented:
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.

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

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°)
##### Thanks for using Experts Exchange.

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