Percentage query. 1

[Aximboy]

How do you get a percentage of an item compare to another item on the same table?

Example: I have a table of pizza ingredients with ingredients and gallon fields.

        Ingredients    Gallon

        cheese             10

        pizza sauce     25

        mushroom        5

How can I query the percentage of cheese from pizza sauce? Answer should be Cheese 40%.

 

[SkipVought]

Hi,

Well 10/25 is .4

But what question are you trying to answer?

One question might be, what is the percentage of cheese in the total ingredients, and that would be 10/(10+25+5) or .25

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Aximboy]

Thanks for your reply.

The question is, what percent is the total gallon of cheese compare to the total gallon of pizza sauce?

 

[dhookom]

Weird question but try:

SELECT Sum(Abs([Ingredient]="cheese")*[Gallons])/Sum(Abs([Ingredient]="Pizza Sauce")*[Gallons]) AS Expr1
FROM tblPizzaIngredients;

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

Thank you for replying, but it is giving me error that says "This expression is typed incorrectly or it is too complex to be evaluated.

 

[dhookom]

What is the exact SQL that you tried and what are the data types of your fields?

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

@dhookom, my bad! I tried it again before posting my SQL query and IT WORKED!!! Thank you so much!!!

I don't know what happened earlier, it's giving me an error message. I might have done something different.

Again, thanks a million!!!