SUM of a column

[cdolphin88]

Hi there,

I have this query below which show me the percentage of the answer, but
I would like to add the total of the percentage, I try to put
SUM(Percentage), but it doesn't work...

Can someone help me?


SELECT IIf(Biological_Damage=0,'Not
present',IIf(Biological_Damage=1,'Slight
Damage',IIf(Biological_Damage=2,'Significant Damage'))) AS
Biological_Damage2, Count([Tb_Types_of_Damage].[Survey_Number]) AS
Items, Count(Biological_Damage)/(SELECT Count(Biological_Damage) From
Types_of_Damage) AS Percentage
FROM Types_of_Damage
GROUP BY [Types_of_Damage].[Biological_Damage];

Cheers!


Claudi

 

[Jerry Whittle]

SELECT
IIf(Biological_Damage=0,'Not present',
IIf(Biological_Damage=1,'Slight Damage',
IIf(Biological_Damage=2,'Significant Damage'))) AS Biological_Damage2,
Count([Tb_Types_of_Damage].[Survey_Number]) AS Items,
Count(Biological_Damage)/(SELECT Count(Biological_Damage) From
Types_of_Damage) AS Percentage
Sum(Count(Biological_Damage)/(SELECT Count(Biological_Damage) From
Types_of_Damage)) AS PercentSum
FROM Types_of_Damage
GROUP BY [Types_of_Damage].[Biological_Damage];

Also you may want to consider a Biological_Damage table with a primary key
field with the 0,1,2 and Damage field of "Not Present", etc. That way you
could link the tables and not have the nasty nested IIf statements.

 

[cdolphin88]

Hi Jerry,

The query didn't work : (

It pops up the error message below:

Cannot have aggregate function in expression
(sum(count(biological_damage)/))

Any idea?

Cheers!

Claudi



Jerry Whittle escreveu:

SELECT
IIf(Biological_Damage=0,'Not present',
IIf(Biological_Damage=1,'Slight Damage',
IIf(Biological_Damage=2,'Significant Damage'))) AS Biological_Damage2,
Count([Tb_Types_of_Damage].[Survey_Number]) AS Items,
Count(Biological_Damage)/(SELECT Count(Biological_Damage) From
Types_of_Damage) AS Percentage
Sum(Count(Biological_Damage)/(SELECT Count(Biological_Damage) From
Types_of_Damage)) AS PercentSum
FROM Types_of_Damage
GROUP BY [Types_of_Damage].[Biological_Damage];

Also you may want to consider a Biological_Damage table with a primary key
field with the 0,1,2 and Damage field of "Not Present", etc. That way you
could link the tables and not have the nasty nested IIf statements.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi there,

I have this query below which show me the percentage of the answer, but
I would like to add the total of the percentage, I try to put
SUM(Percentage), but it doesn't work...

Can someone help me?


SELECT IIf(Biological_Damage=0,'Not
present',IIf(Biological_Damage=1,'Slight
Damage',IIf(Biological_Damage=2,'Significant Damage'))) AS
Biological_Damage2, Count([Tb_Types_of_Damage].[Survey_Number]) AS
Items, Count(Biological_Damage)/(SELECT Count(Biological_Damage) From
Types_of_Damage) AS Percentage
FROM Types_of_Damage
GROUP BY [Types_of_Damage].[Biological_Damage];

Cheers!

Claudi