英文:
simple finance database, need some statistics, how to query?
问题
I want to use a simple private cashflow database. Nothing special.
Therefor I use a table "finance_flow", where I can put all my in- and outcomes.
Income -> amount > 0
outcome -> amount < 0
Table structure
table "finance_flow" with example-data
| id | category_id | amount | date | note |
|---|---|---|---|---|
| int | int | float | timestamp | varchar |
| 1 | 1 | +60.00 | 5.2.23 | use for xy |
| 2 | 2 | -10.00 | 8.2.23 | to Tom for school |
| 3 | 3 | -8.96 | 8.2.23 | milk, bread, cheese |
table "category"
| id | name |
|---|---|
| 1 | tips |
| 2 | kids |
| 3 | shop |
Of course there is a correct foreign-key-constraint.
What I want:
I want some statistical data, for example:
-current status of my money
-total outcomes for each category
-procentual values of those would be nice
I know how to get the total current state:
SELECT sum(amount) as total FROM finance_flow
I know how to get the total per category
SELECT abs(sum(amount)) as total_per_cat, category.name
FROM finance_flow
LEFT JOIN category ON financeflow.cat_id = category.id
GROUP BY category_id [WHERE date = 'february']
(Here I use the function abs(x), because I am not interested in the sign.
The where-clause is optional, I want this, if the basics are correct, for monthly reports.
How to get the proportional values?
Can I get all this stuff with one query? 
Expected result:
proportional_per_cat = total_per_cat / total_income(february) * 100
where
total_per_cat = abs(sum(amount)) for category x
total_income(february) = 60
resulting table:
| name | total_per_cat | proportional_per_cat |
|---|---|---|
| kids | 10 | 16.67 % |
| shop | 8.96 | 14.93 % |
英文:
I want to use a simple private cashflow database. Nothing special.
Therefor I use a table "finance_flow", where I can put all my in- and outcomes.
Income -> amount > 0
outcome -> amount < 0
Table structure
table "finance_flow" with example-data
| id | category_id | amount | date | note |
|---|---|---|---|---|
| int | int | float | timestamp | varchar |
| 1 | 1 | +60,00 | 5.2.23 | use for xy |
| 2 | 2 | -10,00 | 8.2.23 | to Tom for school |
| 3 | 3 | -8,96 | 8.2.23 | milk, bread, cheese |
table "category"
| id | name |
|---|---|
| 1 | tips |
| 2 | kids |
| 3 | shop |
Of course there is a correct foreign-key-constraint.
What I want:
I want some statistical data, for example:
-current status of my money
-total outcomes for each category
-procentual values of those would be nice
I know how to get the total current state:
SELECT sum(amount) as total FROM finance_flow
I know how to get the total per category
SELECT abs(sum(amount)) as total_per_cat, category.name
FROM finance_flow
LEFT JOIN category ON financeflow.cat_id = category.id
GROUP BY category_id [WHERE date = 'february']
(Here I use the function abs(x), because I am not interessted in the sign.
The where-clause is optional, I want this, if the basics are correct, for monthly reports.
How to get the procentual values?
Can I get all this stuff with one query?
Expected result:
procentual_per_cat = total_per_cut / total_income(february) * 100
where
total_per_cut = abs(sum(amount)) for category x
total_income(february) = 60
resulting table:
| name | total_per_cat | procentual_per_cat |
|---|---|---|
| kids | 10 | 16.67 % |
| shop | 8.96 | 14.93 % |
答案1
得分: 1
计算百分比与总金额除法相同：
SELECT
abs(sum(amount)) as total_per_cat,
abs(sum(amount))/(select sum(amount) from finance_flow where amount>0) *100 as 百分比,
category.name
FROM finance_flow
LEFT JOIN category ON finance_flow.cat_id = category.id
WHERE amount<0
GROUP BY category.name
参见：DBFIDDLE
英文:
Calculating the percentage is the same a dividing by the total amount:
SELECT
abs(sum(amount)) as total_per_cat,
abs(sum(amount))/(select sum(amount) from finance_flow where amount>0) *100 as percentage,
category.name
FROM finance_flow
LEFT JOIN category ON finance_flow.cat_id = category.id
WHERE amount<0
GROUP BY category.name
see: DBFIDDLE
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论