SELECT CASE to return accumulative results

Question

I'm trying to create a query using SELECT CASE that would return the accumulative results.

Here is my query, it works but doesnt return the correct result.

  SELECT total, count(*) as count FROM
                       (
                         SELECT case
                         WHEN ( X ) < 1 THEN '1 km'
                         WHEN ( X ) < 3 THEN '3 km'
                         WHEN ( X ) < 5 THEN '5 km'
                         WHEN ( X ) < 10 THEN '10 km'
                         WHEN ( X ) < 15 THEN '15 km'
                         WHEN ( X ) < 25 THEN '25 km'
                         WHEN ( X ) < 50 THEN '50 km'
                         WHEN ( X ) < 100 THEN '100 km'
                         WHEN ( X ) > 0 THEN '-1'
                         else '-2'
                         end AS `total` 
                         FROM `store` AS d WHERE d.pending!='1' 
                    ) AS someRandomAliasHere
                    GROUP BY `total`

X is a formula i'm using to calculate radius from a lat and lang. total is NOT a column in my database table, just a result to calculations of X

The query above gives me this..

 1 km (4)
 3 km (19)
 5 km (103)
 25 km  (540)
 50 km (61)
 ....

4,19,103,540,62 are the total matches found.

The total count for 3 should be 19+4=23.
5 should be 103+19+4=122 etc. And WHEN ( X ) > 0 THEN '-1' should show the total count. of matches

I tried using BETWEEN 0 AND 1, BETWEEN 0 AND 3 etc but it still didn't give me the correct results

Any ideas?

Answer 1

Another approach is to calculate the results independently then union them:

SELECT 1 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 1
UNION ALL
SELECT 3 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 3
UNION ALL
SELECT 5 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 5
UNION ALL
/** ... **/
SELECT 100 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 100

Answer 2

In addition to the accumulation, you also want a total value at the end with -1. This is a bit of a pain, but it can be accomplished.

The simplest way to do cumulative sums in MySQL is using variables. The basic idea is this:

 SELECT total, cnt, (@cnt := @cnt + cnt) as count  
 FROM (SELECT (case  WHEN ( X ) < 1 THEN '1'
                     WHEN ( X ) < 3 THEN '3'
                     WHEN ( X ) < 5 THEN '5'
                     WHEN ( X ) < 10 THEN '10'
                     WHEN ( X ) < 15 THEN '15'
                     WHEN ( X ) < 25 THEN '25'
                     WHEN ( X ) < 50 THEN '50'
                     WHEN ( X ) < 100 THEN '100'
                     WHEN ( X ) > 0 THEN '-1'
                     else '-2'
               end) AS total, COUNT(*) as cnt
        FROM store s
        WHERE s.pending <> '1' 
        GROUP BY total
       ) t CROSS JOIN
       (SELECT @cnt := 0) vars
  ORDER BY total;

The issue with this is that you will not get an overall total of the non-negative values. Let me assume that you have no negative values. This requires adding another row into the total line:

 SELECT total, cnt, (@cnt := @cnt + cnt) as count
 FROM (SELECT (case  WHEN ( X ) < 1 THEN '1'
                     WHEN ( X ) < 3 THEN '3'
                     WHEN ( X ) < 5 THEN '5'
                     WHEN ( X ) < 10 THEN '10'
                     WHEN ( X ) < 15 THEN '15'
                     WHEN ( X ) < 25 THEN '25'
                     WHEN ( X ) < 50 THEN '50'
                     WHEN ( X ) < 100 THEN '100'
                     WHEN ( X ) > 0 THEN '-1'
                     else '-2'
               end) AS total, COUNT(*) as cnt
        FROM store s
        WHERE s.pending <> '1' 
        GROUP BY `total`
        UNION ALL
        SELECT -1, 0
       ) t CROSS JOIN
       (SELECT @cnt := 0) vars
  ORDER BY (total >= 0) desc, total;

I've changed the order by as well. Note that the value -2 is probably meaningless, because X < 1 and X > 0 cover all possible values of X (except for NULL). If you actually have values 100 or greater, there are some small changes to refine the query. You do not describe what to do with those values, so clarification on the question would be helpful.