基于关联表双列聚合成本及近3个月月度总计的SQL查询

Hey there! Let's work through your SQL query requests using the table structures you shared. Below are tailored solutions, plus an explanation of how to aggregate data from those two related columns.

1. Aggregate Total Cost per Incident from crime_incidentid and similar_incidentid #

To calculate the total cost_to_city for each incident (whether it's referenced as a primary crime incident or a similar one), we first need to "unpivot" the two ID columns into a single list of incident IDs. This lets us treat both columns as the same dimension for aggregation.

Here's the SQL:

WITH unpivoted_incidents AS (
    -- Split out both incident ID columns into individual rows
    SELECT crime_incidentid AS incident_id, cost_to_city
    FROM listofincidents
    UNION ALL
    SELECT similar_incidentid AS incident_id, cost_to_city
    FROM listofincidents
)
SELECT 
    incident_id,
    SUM(cost_to_city) AS total_cost_to_city
FROM unpivoted_incidents
GROUP BY incident_id
ORDER BY incident_id;

Quick Notes: #

• The WITH clause creates a temporary CTE (Common Table Expression) that combines both ID columns using UNION ALL (we use ALL to preserve all rows, avoiding unintended deduplication).
• We then group by the unified incident_id and use SUM() to get the total cost for each incident.
• If you want to pull in the incident name from the crimeincidents table, just add a JOIN at the end:

  JOIN crimeincidents ci ON ci.id = unpivoted_incidents.incident_id
  

2. Monthly Total Cost for January, February, and March #

To get the total monthly costs for the first three months of the year, we'll extract the month from incidentdate, filter for the target months, then group and sum the costs.

Here's the query:

SELECT
    MONTH(incidentdate) AS incident_month,
    MONTHNAME(incidentdate) AS month_name,
    SUM(cost_to_city) AS monthly_total_cost
FROM listofincidents
WHERE MONTH(incidentdate) IN (1, 2, 3)
-- Uncomment below if you need to target a specific past year (e.g., last year)
-- AND YEAR(incidentdate) = YEAR(CURRENT_DATE) - 1
GROUP BY incident_month, month_name
ORDER BY incident_month;

Quick Notes: #

• MONTH() pulls the numeric month value, while MONTHNAME() returns the full name (for readability).
• The WHERE clause filters for January (1), February (2), and March (3). If you need to avoid including data from other years, add a YEAR() condition to narrow it down.
• Grouping by both the numeric month and month name ensures proper aggregation, and ordering by incident_month keeps results in chronological order.

How to Aggregate Data from Two Related Columns in a Table #

When dealing with two columns that reference the same related table (like crime_incidentid and similar_incidentid both linking to crimeincidents.id), the key step is to convert multi-column同类数据 into a single-column dataset (unpivoting):

• Use UNION ALL to combine the two columns into one list of IDs, keeping their associated cost values intact.
• Once the data is in this flat structure, you can use standard grouping and aggregation functions (SUM(), COUNT(), etc.) just like you would with any single-column dimension.
• If you need to join with the related table (e.g., to get incident names), you can do so either before or after the aggregation—whichever makes more sense for your use case.

内容的提问来源于stack exchange，提问作者Philip E