CODEWITHSUNDEEP
mysqlsqldatabase
Philip E
Philip E

Reputation: 858

How do i aggregate data from 2 columns referencing another table?

Using the data below, how do i get the sum of the cost of each incident based on 2 columns(crime_incidentid, similar_incidentid) in the listofincidents table. 

  create table crimeincidents
    (
        id int not null,
        name varchar(20),
        primary key (id)
    );
    create table listofincidents
    (
        id int 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, crime_incidentid, similar_incidentid, cost_to_city) 
    values (1, 1, 2, 900), (2, 2, 3, 800), (3, 3, 1, 1500.10), (4, 1, 3, 800.23);


I want to get an aggregate data similar to the table below.
---------------------------------------------------------------
id  name             | similarIncidentCost  | crimeIncidentCost
1   Burglary         | 1500.10              | 1700.23
2   Theft            |  900                 | 800
3   Grand Theft Auto |  1600.23             | 1500.1

Upvotes: 1

Views: 71

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

For safest side you would need to do the join with separate result set containing for similarIncidentCost and crimeIncidentCost

select c.id, 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

In similar way, you could also explore via union all

select id, sum(case when type = 'sim' then cost_to_city else 0 end) similarIncidentCost,
           sum(case when type = 'cr' then cost_to_city else 0 end) crimeIncidentCost
from
(
    select c.id, s.cost_to_city, 'sim' as type
    from crimeincidents c left join listofincidents s 
                          on s.similar_incidentid   = c.id
    union all
    select c.id, cr.cost_to_city, 'cr' as type
    from crimeincidents c left join listofincidents cr 
                          on cr.crime_incidentid   = c.id
)t
group by id

Upvotes: 2

Monah
Monah

Reputation: 6784

you can use the following

with report as(
  select c.id,c.name,sum(cost_to_city) as total,Type='crimeIncidentCost'
  from crimeincidents c
  inner join listofincidents l on l.crime_incidentid = c.id
  group by c.id,c.name
  union all
  select c.id,c.name,sum(cost_to_city) as total,Type='similarIncidentCost'
  from crimeincidents c
  inner join listofincidents l on l.similar_incidentid = c.id
  group by c.id,c.name)
select id,name, [similarIncidentCost],[crimeIncidentCost]
from report r
pivot( sum(total) for Type in([similarIncidentCost],[crimeIncidentCost])) p

Here a working demo

Result

id  name    crime_incidentid    similar_incidentid
1   Burglary            1700.23             1500.1
3   Grand theft auto    1500.1              1600.23
2   Theft               800                 900

Hope this will help you

Upvotes: 0

Related Questions