38512

How do i aggregate data from 2 columns referencing another table and also get the monthly totals for

Question:

Using the data below:

<ol><li>

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

</li> <li>

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); </li> </ol>

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

Answer1:

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

Recommend

  • What is the time delay between getting data and loading to UITableView
  • Add Elements And Node in XML
  • format exception was unhandled input string was not in a correct formats
  • How to replace a decimal in a number with a string?
  • How to convert one data type to another
  • What does 'this' mean in a c# constructor? [duplicate]
  • How to enforce custom MySQL column format
  • pandas parse csv with left and right quote chars
  • I don't get what's the difference between format() and … (python)
  • How to remove the dot in to_char if the number is an integer
  • Pointer vs Reference difference when passing Eigen objects as arguments
  • LNK1104: cannot open file 'kernel32.lib'
  • how do you obtain the address of an instance after overriding the __str__ method in python
  • Many to Many in Linq using Dapper
  • Extending the Django 1.11 User Model
  • How to Model Ternary Relationship in CakePhp?
  • Are there any side effects from calling SQLAlchemy flush() within code?
  • Can my PDF ping my server when it is opened?
  • Google Places API - Find a company's CID and LRD
  • Pycharm: Marking a folder as 'sources root' is not recursive for subfolders
  • How to detect interior vertices in groups of 2d polygons? (E.g. ZIP Codes to determine a territory)
  • Convert Type Decimal to Hex (string) in .NET 3.5
  • Transactional Create with Validation in ServiceStack Redis Client
  • Why querying a date BC is changed to AD in Java?
  • ilmerge with a PFX file
  • Why value captured by reference in lambda is broken? [duplicate]
  • PHP - How to update data to MySQL when click a radio button
  • Join two tables and save into third-sql
  • Deserializing XML into class C#
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Buffer size for converting unsigned long to string
  • Understanding cpu registers
  • Why joiner is not used after Sequence generator or Update statergy
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • UserPrincipal.Current returns apppool on IIS
  • How can I use threading to 'tick' a timer to be accessed by other threads?