mysql - 如何按类别分组，然后求收入的百分比？

Question

我有三个这样的表

CREATE TABLE  `money`.`categories` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Title` text NOT NULL,
  PRIMARY KEY (`ID`)
)

CREATE TABLE  `money`.`expenditure` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Purpose` text NOT NULL,
  `Amount` bigint(20) unsigned NOT NULL,
  `Date` datetime NOT NULL,
  `CategoryID` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_expenditure_1` (`CategoryID`),
  CONSTRAINT `FK_expenditure_1` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`ID`)
)

CREATE TABLE  `money`.`income` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Source` text NOT NULL,
  `Amount` bigint(20) unsigned NOT NULL,
  `Date` datetime NOT NULL,
  PRIMARY KEY (`ID`)
)

其实一点都不复杂。非常简单。

我有三个查询要查找

1. 特定月份的收入总和:
   从 money.income ic 中选择 sum(amount) tot_inc where month(ic.date) = 02;

2. 特定月份的支出总和:
   从 money.expenditure ex 中选择 sum(amount) tot_exp where month(ex.date) = 02;

3. 特定类别中特定月份的支出总和:
   从 money.expenditure ex 选择 sum(amount) tot_exp where month(ex.date) = 02 and ex.categoryid = 3;

我有两个问题:

1. 如何找出特定月份总支出中消失的总收入百分比。基本上我想从查询 1 和查询 2 中找出 (100*tot_exp/tot_inc)。但是我如何在查询中写它？

2. 在此基础上，我想了解每个类别的支出在特定月份中所占的百分比。我知道它是分组依据和聚合和/或数学函数的组合，但我什至无法开始想象应该如何编写查询。我怎样才能得到这些数字？

Answer 1

SELECT 
(100*(SELECT SUM(amount) tot_inc FROM money.income ic WHERE month(ic.date) = "02")/SUM(amount)) AS "Result"
FROM expenditure e 
WHERE MONTH(ex.date) = "02"
GROUP BY CategoryID
HAVING result > 10

我觉得应该是这样的