How Do I Aggregate Data From 2 Columns Referencing Another Table And Also Get The Monthly Totals For The Past 3 Months?

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

Solution 1:

I think this is what you're asking for :

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
GROUPBYdate, ci.id
ORDERBYdate

And you can go with :

SELECT CONCAT(YEAR(li.incidentdate), ' ', MONTHNAME(li.incidentdate)) asmonth,
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
GROUPBYmonth, ci.id
ORDERBYmonth

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)) asmonth,
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
GROUPBYmonth, ci.id
ORDERBYmonth