SUM of a column

  • Thread starter Thread starter cdolphin88
  • Start date Start date
C

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

[email protected] said:
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
Click to expand...
Click to expand...
 
Back
Top