I am trying to get my percentages to equal 100%. As of right now they far surpass 100%.
I need to account for the percentages in two different tables, ua_money_allocation
and ua_expenses_by_school
.
I am not sure how I can add these two tables in properly without getting:
error code 1241, "Operand should contain 1 column(s)"
Query:
SELECT 'Salaries and Wages' as 'Category',
CONCAT('$',ROUND(SUM(Value_USD),0)) AS 'Amount',
CONCAT('$',ROUND(SUM(Value_USD)/(COUNT(DISTINCT ua_money_allocation.school_ID_FK)))) AS 'Mean',
(ROUND(SUM(ua_money_allocation.Value_USD)/
(SUM(ua_money_allocation.Value_USD)*100,1 +
SUM(ua_expenses_by_school.Amount_USD)))) AS 'Percent of Total',
CONCAT('$',ROUND(Min(Value_USD),0)) AS 'Minimum',
CONCAT('$',ROUND(MAX(Value_USD),0)) AS 'Maximum'
FROM ua_money_allocation
Join ua_expenses_by_school
ON ua_money_allocation.school_ID_FK = ua_expenses_by_school.school_id_fk
WHERE ua_money_allocation.Report_yr='2015'
AND Value_USD !=0
AND Amount_USD!=0
AND Spending_Activity='Total Spent'
UNION ALL
SELECT Category,
CONCAT('$',ROUND(SUM(Amount_USD),0)) AS 'Amount',
CONCAT('$',ROUND(SUM(Amount_USD)/(COUNT(DISTINCT ua_expenses_by_school.school_ID_FK)))) AS 'Mean',
(ROUND(SUM(ua_expenses_by_school.Amount_USD)/
(SUM(ua_expenses_by_school.Amount_USD)*100,1 +
SUM(ua_money_allocation.Value_USD)))) AS 'Percent of Total',
CONCAT('$',ROUND(Min(Amount_USD),0)) AS 'Minimum',
CONCAT('$',ROUND(MAX(Amount_USD),0)) AS 'Maximum'
FROM ua_expenses_by_school
JOIN ua_money_allocation
ON ua_expenses_by_school.school_id_fk = ua_money_allocation.school_ID_FK
WHERE ua_expenses_by_school.Report_yr='2015'
AND Amount_USD !=0
AND Value_USD !=0
AND Spending_Activity='Total Spent'
group by category
Best Answer
Stare at this: