Search code examples
phpmysqlsqldatabaserdbms

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:

  1. How do I get the sum of the cost of each incident based on two columns (crime_incidentid, similar_incidentid) in the listofincidents table?

  2. 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

Solution

  • 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
    GROUP BY date, ci.id
    ORDER BY date
    

    And you can go with :

    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