jmatthewwhite
Posts: 6
|
| Posted: 07/13/2007, 10:14 AM |
|
I have generated a report which retrieves records from a database. Each record has 12 columns (fields). I need to get some percentages based on the combined sums of four columns. I can get sums for each of the four individual columns but I cannot get a combined sum and cannot get each column's percentage of the combined sum. For example, I can get labels to display:
Column 1: Sum of 5
Column 2: Sum of 10
Column 3: Sum of 15
Column 4: Sum of 20
I cannot get labels to display:
Grand Total: 50
Column 1 is 10% of the Grand Total
Column 2 is 20% of the Grand Total
Column 3 is 30% of the Grand Total
Column 4 is 40% of the Grand Total
I cannot get a label to display the grand total and I cannot get a label to display each column's percentage.
Any ideas?
Thanks in advance.
|
 |
 |
DonB
|
| Posted: 07/13/2007, 3:00 PM |
|
SELECT SUM(col1) + SUM(col2) + SUM(col3)+ SUM(col4) AS grandtotal,
SUM(col1) / (SUM(col1) + SUM(col2) + SUM(col3)+ SUM(col4)) as pct1,
SUM(col2) / (SUM(col1) + SUM(col2) + SUM(col3)+ SUM(col4)) as pct2,
SUM(col3) / (SUM(col1) + SUM(col2) + SUM(col3)+ SUM(col4)) as pct3,
SUM(col4) / (SUM(col1) + SUM(col2) + SUM(col3)+ SUM(col4)) as pct4
FROM ...
WHERE ...
GROUP BY ...
--
DonB
http://www.gotodon.com/ccbth
"jmatthewwhite" <jmatthewwhite@forum.codecharge> wrote in message
news:24697b2e357e15@news.codecharge.com...
> I have generated a report which retrieves records from a database. Each
record
> has 12 columns (fields). I need to get some percentages based on the
combined
> sums of four columns. I can get sums for each of the four individual
columns
> but I cannot get a combined sum and cannot get each column's percentage
of the
> combined sum. For example, I can get labels to display:
>
> Column 1: Sum of 5
> Column 2: Sum of 10
> Column 3: Sum of 15
> Column 4: Sum of 20
>
> I cannot get labels to display:
>
> Grand Total: 50
>
> Column 1 is 10% of the Grand Total
> Column 2 is 20% of the Grand Total
> Column 3 is 30% of the Grand Total
> Column 4 is 40% of the Grand Total
>
> I cannot get a label to display the grand total and I cannot get a label
to
> display each column's percentage.
>
> Any ideas?
>
> Thanks in advance.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
jmatthewwhite
Posts: 6
|
| Posted: 07/19/2007, 12:22 PM |
|
Don,
First of all, sorry about taking so long to reply. I have been out of town. Second, thank you for your reply. I appreciate it. However, I am still having some problems. I am not getting a column sum for all the records returned by the query. I am only getting a sum of the columns for each individual record.
This:
SUM(assessment_belowbasic)+SUM(assessment_basic)+SUM(assessment_proficient)+SUM(assessment_advanced) AS grandtotal
is returning the same as this:
(assessment_belowbasic)+(assessment_basic)+(assessment_proficient)+(assessment_advanced) AS grandtotal
I don't know what I am doing wrong.
Thanks for any help you could provide.
Matt
|
|
|
|
|
