如何汇总来自引用另一张表的2列的数据,并获得过去3个月的每月总计? [英] How do i aggregate data from 2 columns referencing another table and also get the monthly totals for the past 3 months?
问题描述
使用以下数据:
-
如何基于两列获取每次事件的费用总和(
事件列表
表中的Crime_incidentid,类似事件)?
How do I get the sum of the cost of each incident based on two columns (crime_incidentid, similar_incidentid) in the
listofincidents
table?
我又如何获得过去的总和? 3个月(1月,2月和3月)?
Also how do I get the sums for the past 3 months (January, February and March)?
create table crimeincidents (
id int not null,
name varchar(20),
primary key (id)
);
create table listofincidents (
id int not null,
incidentdate datetime not null,
crime_incidentid int not null,
similar_incidentid int not null,
cost_to_city decimal(8,2),
primary key (id),
FOREIGN KEY (crime_incidentid) REFERENCES crimeincidents(id),
FOREIGN KEY (similar_incidentid) REFERENCES crimeincidents(id)
);
insert into crimeincidents (id,name) values
(1,'Burglary'),
(2,'Theft'),
(3,'Grand theft auto');
insert into listofincidents (id, incidentdate, crime_incidentid,
similar_incidentid, cost_to_city)
values
(1, "2018-01-10 18:48:00", 1, 2, 900),
(2, "2018-02-15 14:48:00", 2, 3, 800),
(3, "2018-02-20 18:10:00", 3, 1, 1500.10),
(4, "2018-03-20 18:48:00", 1, 3, 800.23),
(5, "2018-03-25 18:24:00", 1, 3, 200.00),
(6, "2018-04-15 10:12:00", 1, 2, 400.00);
用于生成没有月度日期的结果的查询是:
The query to generate the results without monthly dates is:
select c.id, c.name, sm.similarIncidentCost, cr.crimeIncidentCost
from crimeincidents c
inner join (
select c.id, sum(s.cost_to_city) similarIncidentCost
from crimeincidents c inner join listofincidents s
on s.similar_incidentid = c.id
group by c.id
) sm on sm.id = c.id
inner join (
select c.id, sum(cr.cost_to_city) crimeIncidentCost
from crimeincidents c inner join listofincidents cr
on cr.crime_incidentid = c.id
group by c.id
) cr on cr.id = c.id;
我想使用过去3个月的数据生成费用。最终结果应如下所示:
I want to generate the costs using the past 3 months data. The final result should look like this:
1. January | 1500.1 | 1900.23
2. February | 900 | 800
3. March | 1800.23 | 1500.1
推荐答案
我认为这就是您要的:
SELECT DATE_FORMAT(li.incidentdate, '%Y-%m') as date,
ci.name,
SUM(
li.cost_to_city
) as totalCost
FROM crimeincidents ci
JOIN listofincidents li ON ci.id = li.crime_incidentid OR ci.id = li.similar_incidentid
GROUP BY date, ci.id
ORDER BY date
可以搭配使用:
SELECT CONCAT(YEAR(li.incidentdate), ' ', MONTHNAME(li.incidentdate)) as month,
ci.name,
SUM(
li.cost_to_city
) as totalCost
FROM crimeincidents ci
JOIN listofincidents li ON ci.id = li.crime_incidentid OR ci.id = li.similar_incidentid
GROUP BY month, ci.id
ORDER BY month
为更好地满足您的要求。
To match your request better.
起初没有注意到您希望将事件和类似事件的总和分开。尽管我觉得很奇怪(因为类似的事件本人也可以发生类似的事件),但我还是进行了查询:
Didn't notice at first you wanted "incident" and "similar incident" sums separated. Although I find it weird (since a similar incident can himself have a similar incident) I did the query :
SELECT CONCAT(YEAR(li.incidentdate), ' ', MONTHNAME(li.incidentdate)) as month,
ci.name,
SUM(
IF(ci.id = li.id, li.cost_to_city,0)
) as totalCostIncident,
SUM(
IF(ci.id = li.similar_incidentid, li.cost_to_city,0)
) as totalCostSimilarIncident
FROM crimeincidents ci
JOIN listofincidents li ON ci.id = li.crime_incidentid OR ci.id = li.similar_incidentid
GROUP BY month, ci.id
ORDER BY month
这篇关于如何汇总来自引用另一张表的2列的数据,并获得过去3个月的每月总计?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!