Below table is my favorite result (please click on image to enlarge it) :
I have problem with calculation of weightPercent column :
In above table (20) is calculated as described below :
(200/(200+300)) * (100/200) * 100 = 20
So query should be something like :
SELECT projectID
,projectName
,orderAmount
,delivered
,((orderAmount/([B][COLOR="Red"]?[/COLOR][/B])) * (delivered/orderAmount) * 100) AS weightPercent
Group By projectName;
I dont know how should I interact with ([B][COLOR="Red"]?[/COLOR][/B]) mark in above query ? I need to calculate that summation for every project separately . [COLOR="Blue"]P.S :[/COLOR] Above query is a abridgement of one complicated query . I tested [U]SUM(DISTINCT *)[/U] but I got many problems with it , so Im looking for any other solution ;Is there any other trick for it ?
I have placed one screenshot that shows the final result; those numbers are description of how (20) is obtained from first row of that table.That is just one example to show how table should work to calculate “weightPercent”.
SELECT daTable.projectID
, daTable.projectName
, daTable.orderAmount
, daTable.delivered
, daTable.delivered * 100.0 /
subtotals.project_amount AS weightPercent
FROM ( SELECT projectName
, SUM(orderAmount) AS project_amount
FROM daTable
GROUP
BY projectName ) AS subtotals
INNER
JOIN daTable
ON daTable.projectName = subtotals.projectName