Calculating a Percentage...

[PHP_GreenHorn]

Hi All,

 

I'm trying to calculate the percentage of each column (i.e. employee discount), I'm using the SUM() function to get the total of each column (i.e. employee discount from each department). The total percentage cannot exceed the 100% range.

 

Thanks in advance.

 

 

 

[Barand]

To get A as a percentage of B:

 

A / B * 100

 

We are not mind readers. If you need any more then you need to give us more, such as

 

what are A and B in this scenario?

what is your current query?

what is the table structure?

[Psycho]

I agree with Barand, you left out crucial information. However, I assume you want to show the percentage for a particular field for each record in the table. E.g. with the following table

 

Name | Sales
-------------
Bob  | 500
Jim  | 1000
Dave | 2000
Alex | 1500

 

You want the results:

Bob: 10%

Jim: 20%

Dave: 40%

Alex: 30%

 

There are a few ways to do this:

 

1. If you are going to be getting all records anyway, then just calculate the total when you process the result set and use that to also calculate the percentages. E.g. as you iterate over each record, dump into a temp array and add each value to a running total. Then use that array to generate the output along with the final total to calculate percentage.

 

2. If you are only going to be pulling a subset of records but need to calculate percentage on all the records, you can run a separate query to get the total and then:

  A) Calculate the percentage in the query to get the records SELECT name, sales, (sales / :total) as percent

  B) As above, use the total during the processing of the records when generating the output

 

3) Create a subquery in the SELECT list to return the percentages. I think this could be a performance concern if you have a lot of data

SELECT name, sales,
    (sales / (SELECT SUM(sales) FROM table_name)) as percent
FROM table_name

[PHP_GreenHorn]

This is what i have as my Code:

 

$query = "SELECT age, consumer_spending_discount, (consumer_spending_discount / (SELECT SUM(consumer_spending_discount) FROM table name)) * 100 AS myspend  FROM table_name GROUP BY age";

 

My Database:

AGE    Consumer Spending Discount

------    ----------------------------------------

13             10%

20             30%

60             90%

20             10%

13             10%

20             40%

 

 

for example:

i'm get 3.2% for age group 13 ???

i'm get 9.7% for age group 20 ???

 

my goal is to find the percentage of each age group spending discount?

 

Thanks in advance

[Sepodati]

For the data group you listed, what exactly are you expecting to see? Age group 20 has three values... what do you want the result of your query to be based on this data?

 

My Database:
AGE    Consumer Spending Discount
------    ----------------------------------------
13             10%
20             30%
60             90%
20             10%
13             10%
20             40%

[Psycho]

my goal is to find the percentage of each age group spending discount?

 

Those values you presented as the data in the table are already percentages. To my knowledge there is no such field type. So what, exactly, is the field type and values in the discount field? Are they decimals: .1, .3, etc? Or worse, are those values stored as text?!