Combining SQL SUM clauses

Question

I have a script that requires executing four rather large MySQL queries on the same subset of data. Is there a way to combine them into one query?

Here is what the query looks like:

Select sum(value) From 
    ( Select lat, lng, value From `pop_geo_199` Where (
         (lat Between 38.1768916977 And 39.6131083023) And (lng Between -77.9596650363 And -76.1143349637)) 
        ) As FirstCut 
Where (acos(0.627895140732*sin(radians(lat)) + 0.778297945677*cos(radians(lat))*cos(radians(lng)-(-1.34454929586))) * 6371 < 79.85)

As you can probably tell, this is a geographic query of latitude and longitude points. What the query does is first create a simple square subset of the total table (FirstCut), and then runs the trig functions on that to get a circular area.

From what I can tell, the part of the query that is slow is the FirstCut, because the table it is drawing on has some 2.8 million rows. FirstCut, though, in this instance, only has about 27,922 rows, so the trig part goes super fast by comparison.

The issue is, I have to run a few of these. They can all use the same FirstCut, though, since they are different radii centered on the same area. I'd love to be able to pull this off with just one query instead of four.

Here is what a second query looks like:

Select sum(value) From 
    ( Select lat, lng, value From `pop_geo_199` Where (
         (lat Between 38.1768916977 And 39.6131083023) And (lng Between -77.9596650363 And -76.1143349637)) 
        ) As FirstCut 
Where (acos(0.627895140732*sin(radians(lat)) + 0.778297945677*cos(radians(lat))*cos(radians(lng)-(-1.34454929586))) * 6371 < 48.57) 

As you can see it is exactly the same as the other one except the last WHERE clause is slight different — the condition is just a smaller radii (48.57 instead of 79.85).

How can I combine these two queries into one query in an efficient way?

I've tried using CASE clauses -- is this the best approach?

Select 

sum(case when (acos(0.627895140732*sin(radians(lat)) + 0.778297945677*cos(radians(lat))*cos(radians(lng)-(-1.34454929586))) * 6371 < 79.85) then value else 0 end),

sum(case when (acos(0.627895140732*sin(radians(lat)) + 0.778297945677*cos(radians(lat))*cos(radians(lng)-(-1.34454929586))) * 6371 < 48.57) then value else 0 end)

From ( Select lat, lng, value From `pop_geo_199` Where ((lat Between 38.1768916977 And 39.6131083023) And (lng Between -77.9596650363 And -76.1143349637)) ) As FirstCut;

Answer 1

You can use a CASE statement for this. You can also move the calculation into the subquery:

Select 
     sum(case 
           when rad < 79.85
           then value 
           else 0 end)  1stQuerySum,
     sum(case 
           when rad < 48.57 
           then value 
           else 0 end)  2ndQuerySum
From  ( 
    Select lat, lng, value, 
           acos(0.627895140732*sin(radians(lat)) + 0.778297945677*cos(radians(lat))*cos(radians(lng)-(-1.34454929586))) * 6371 rad
    From `pop_geo_199` 
    Where (
         (lat Between 38.1768916977 And 39.6131083023) And 
         (lng Between -77.9596650363 And -76.1143349637)
    ) 
) As FirstCut