AVG in query with condition

[If]

Hello,
I have a table with 3 Fields (Name, Time, Indicator)

Name Time Indicator
----------------------------
Name1 0,... 1...
Name1 0,... 3...
Name3 0,... 2...
Name2 0,... 1...
Name2 0,... 3...
Name1 0,... 2...
....

The Field Time is numéric (0,...)


The querie below calculates the number of Indicator value by Name.

SELECT Datas.Name,
Sum(IIf([Indicator] Like "*",1,0)) AS [Total],
Sum(IIf([Indicator] Like "1*",1,0)) AS Total_1,
Sum(IIf([Indicator] Like "2*",1,0)) AS Total_2,
Sum(IIf([Indicator] Like "3*",1,0)) AS Total_3,
FROM Datas
GROUP BY Datas.Name;


Now, I would like to add a calculation to this query.
I want to calculate the average of the field "Time".

I have this line which makes the average but on all the field "Time".
Avg(Datas.oper_time) AS Average_Time

Thus I would like to know the average of the field "Time" when the field
"Indicator" contains 1... , 2..., 3...


Thank you in advance for your help

 

[Marshall Barton]

I have a table with 3 Fields (Name, Time, Indicator)

Name Time Indicator
----------------------------
Name1 0,... 1...
Name1 0,... 3...
Name3 0,... 2...
Name2 0,... 1...
Name2 0,... 3...
Name1 0,... 2...
...
The Field Time is numéric (0,...)

The querie below calculates the number of Indicator value by Name.

SELECT Datas.Name,
Sum(IIf([Indicator] Like "*",1,0)) AS [Total],
Sum(IIf([Indicator] Like "1*",1,0)) AS Total_1,
Sum(IIf([Indicator] Like "2*",1,0)) AS Total_2,
Sum(IIf([Indicator] Like "3*",1,0)) AS Total_3,
FROM Datas
GROUP BY Datas.Name;

Now, I would like to add a calculation to this query.
I want to calculate the average of the field "Time".

I have this line which makes the average but on all the field "Time".
Avg(Datas.oper_time) AS Average_Time

Thus I would like to know the average of the field "Time" when the field
"Indicator" contains 1... , 2..., 3...

You can do it using the same idea, except you have to use
Null instead of 0. Null will work in Sum (and all the
aggregate functions) too.

Avg(IIf(Indicator Like "1*", Indicator, Null)) AS Avg_1

BTW, it's a waste of time to use Like "*"
Sum(1) AS [Total]
Or more simply:
Count(*)
Or, if Indicator could be Null in some records:
Count(Indicator)

The other expressions could also use Null if you are so
inclined:
Count(IIf(Indicator Like "3*", 1, Null)) AS Total_3
Here, the 1 can be any non-null constant. This produces the
same result:
Count(IIf(Indicator Like "3*", "Three", Null)) AS Total_3