Using the data below:
How do I get the sum of the cost of each incident based on two columns (crime_incidentid, similar_incidentid) in the
listofincidents
table?-
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;
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