How do I Combine these SQL SELECT queries into one SELECT statement

Question

How do I combine these two select statements into one query:

SELECT SUM( incidents )  AS fires, neighborhoods AS fire_neighborhoods
FROM (
SELECT * 
FROM `fires_2009_incident_location` 
UNION ALL SELECT * 
FROM `fires_2008_incident_location`
UNION ALL SELECT * 
FROM `fires_2007_incident_location`
UNION ALL SELECT * 
FROM `fires_2006_incident_location`
) AS combo
GROUP BY fire_neighborhoods ORDER BY fires DESC



SELECT SUM( incidents )  AS adw, neighborhoods AS adw_neighborhoods
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo2
GROUP BY adw_neighborhoods ORDER BY adw DESC

So, I'd like the query to return, something like:

fire_neighborhoods  fires  adw_neighborhoods  adw
xyzNeighborhood     6      abcNeighborhood    22
jklNeighborhood     3      tuvNeighborhood    40

I want to simply combine the results of the two queries above. The two queries are independent of each other. The results of one doesn't effect the results of the other query. I simply need a way to slam the two results together into one.

If anyone has any advice, please let me know.

Thank you.

-Laxmidi

Answer 1

Thanks for the the help in figuring this out. Shout out to David Hall, Aaron, Jeffrey Whitledge & NYSystemsAnalyst. I went with the dummy column option:

SELECT SUM( incidents ) , neighborhoods,  'adw' as offense 
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo 
GROUP BY neighborhoods  
UNION ALL
SELECT SUM( incidents ), neighborhoods,  'fire' as offense 
FROM (
SELECT * 
FROM `fire_2009_incident_location` 
UNION ALL SELECT * 
FROM `fire_2008_incident_location`
UNION ALL SELECT * 
FROM `fire_2007_incident_location`
UNION ALL SELECT * 
FROM `fire_2006_incident_location`
) AS combo2
GROUP BY neighborhoods 

Answer 2

Displaying two unrelated queries side-by-side is usually the job of the reporting software rather than the SQL engine. The problem here is that you are asking the query tool to do something that it is not suited to do: display formatting.

A relation (which is what the result of a SELECT is supposed to be, fundamentally) is a data structure that makes assertions about the state of the world. Each row defines a series of propositions which are (ideally) true.

In your question, the rows contain arbitrary concatinations of facts which do not relate to one another and about which no propositions can be stated.

Answer 3

The answers given by the others of using a discriminator column look like what you are after, but just in case, it is possible to add dummy place holder columns to unions as shown below:

SELECT 
    SUM( incidents )  AS fires, 
    neighborhoods AS fire_neighborhoods,
    0 as adw,
    '' as adw_neighbourhoods
FROM ( 
    SELECT *  
    FROM `fires_2009_incident_location`  
    UNION ALL SELECT *  
    FROM `fires_2008_incident_location` 
    UNION ALL SELECT *  
    FROM `fires_2007_incident_location` 
    UNION ALL SELECT *  
    FROM `fires_2006_incident_location` 
) AS combo 
GROUP BY fire_neighborhoods ORDER BY fires DESC 

UNION 

SELECT 
    0 as fires,
   '' as fire_neighbourhoods,
    SUM( incidents )  AS adw, 
    neighborhoods AS adw_neighborhoods 
FROM ( 
    SELECT *  
    FROM `adw_2009_incident_location`  
    UNION ALL SELECT *  
    FROM `adw_2008_incident_location` 
    UNION ALL SELECT *  
    FROM `adw_2007_incident_location` 
    UNION ALL SELECT *  
    FROM `adw_2006_incident_location` 
) AS combo2 
GROUP BY adw_neighborhoods ORDER BY adw DESC

Answer 4

The example you gave indicates you want to combine the queries horizontally, but then you later stated they are completely independent. These are conflicting statements because you normally combine data horizontally when records do relate to one another. Below is my idea for combining them horizontally, but I also make note of my idea for combining them vertically below that.

It depends how you want to link them up. If you are querying based on neighborhood, you can do a join between the two larger queries on fire_neighborhoods = adw_neighborhoods, such as:

SELECT fire_neighborhoods, fires, adw
FROM (

SELECT SUM( incidents )  AS fires, neighborhoods AS fire_neighborhoods
FROM (
SELECT * 
FROM `fires_2009_incident_location` 
UNION ALL SELECT * 
FROM `fires_2008_incident_location`
UNION ALL SELECT * 
FROM `fires_2007_incident_location`
UNION ALL SELECT * 
FROM `fires_2006_incident_location`
) AS combo
GROUP BY fire_neighborhoods ORDER BY fires DESC

) AS fires
    INNER JOIN (  

SELECT SUM( incidents )  AS adw, neighborhoods AS adw_neighborhoods
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo2
GROUP BY adw_neighborhoods ORDER BY adw DESC

) AS adw
    ON fires.fire_neighborhoods = adw.adw_neighborhoods

This is just an example. You may need a different join or something to make it work for you.

Now, you stated that the two queries are independent and do not affect one another. If they really do have no common ground, you should add a column to each query indicating the query it came from (e.g. add a column with a constant value of 1 for the fire query and a column with a constant value of 2 for the adw query). Then, just UNION the two large queries together. This would combine them in a vertical fashion as opposed to a horizontal fashion.

Answer 5

If I understand what you need correctly, you can UNION the queries together, adding a column to let you know which query it came from:

SELECT SUM( incidents )  AS fires_or_adw, neighborhoods AS fire_or_adw_neighborhoods, 'Fires' as which_query
FROM (
SELECT * 
FROM `fires_2009_incident_location` 
UNION ALL SELECT * 
FROM `fires_2008_incident_location`
UNION ALL SELECT * 
FROM `fires_2007_incident_location`
UNION ALL SELECT * 
FROM `fires_2006_incident_location`
) AS combo
GROUP BY fire_neighborhoods ORDER BY fires DESC

UNION

SELECT SUM( incidents )  AS fires_or_adw, neighborhoods AS fire_or_adw_neighborhoods, 'ADW' as which_query
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo2
GROUP BY adw_neighborhoods ORDER BY adw DESC

Granted you'll have twice as many rows and half your data will be null, but just "slamming" the results together as in your example isn't usual.