2

Update: Re-phrasing the question.

Below are the results and the desired results. What do I need to do to get the desired results? See http://sqlfiddle.com/#!2/d0e13c/69

Thank you

SELECT CONCAT('Greater than or equal to ',t.val2,':', count(*)) FROM
(
SELECT 
  CASE 
    WHEN val>=1000 THEN 1000
    WHEN val>=500 THEN 500
    WHEN val>=400 THEN 400
    WHEN val>=300 THEN 300
    WHEN val>=200 THEN 200
    WHEN val>=100 THEN 100 
    WHEN val>=10 THEN 10
    WHEN val>=5 THEN 5
    WHEN val>=2 THEN 2
    WHEN val>=1 THEN 1
  END AS 'val2'
FROM e
) as t
GROUP BY t.val2
ORDER BY t.val2 DESC 

Results:

Greater than or equal to 1000:1
Greater than or equal to 500:1
Greater than or equal to 400:2
Greater than or equal to 300:1
Greater than or equal to 200:2
Greater than or equal to 100:3
Greater than or equal to 10:1
Greater than or equal to 5:1
Greater than or equal to 1:2

Desired results:

Greater than or equal to 1000:1
Greater than or equal to 500:2
Greater than or equal to 400:4
Greater than or equal to 300:5
Greater than or equal to 200:7
Greater than or equal to 100:10
Greater than or equal to 10:11
Greater than or equal to 5:12
Greater than or equal to 2:12
Greater than or equal to 1:14
  • 2
    Why aren't you using ints in the first place? – Mat May 9 '14 at 9:05
  • And what does this mean exactly?: "My query fails to sort correctly when casting string as int." – ypercubeᵀᴹ May 9 '14 at 9:29
  • The CASE statament stops at the first condition evaluated as True, as if there is an implicit break. – Serpiton May 10 '14 at 23:17
2

It's possible to convert the CASE options into rows of a temp table, a JOIN with a less or equal condition to the data table will get the rows.

SELECT CONCAT('Greater than or equal to ',a.val,':', count(e.val))
FROM   (Select 1000 val UNION ALL 
        Select 500      UNION ALL
        Select 400      UNION ALL
        Select 300      UNION ALL
        Select 200      UNION ALL
        Select 100      UNION ALL
        Select 10       UNION ALL
        Select 5        UNION ALL
        Select 2        UNION ALL
        Select 1       ) a
       LEFT JOIN e ON e.val >= a.val
GROUP BY a.val
ORDER BY a.val  DESC

SQLFiddle demo

Another way doesn't even need the temp table, but will get the results as columns instead of as rows, if you care about the results and not about the format

SELECT CONCAT('Greater than or equal to 1000: '
             , SUM(Case When val >= 1000 Then 1 Else 0 End)) _1000
     , CONCAT('Greater than or equal to  500: '
            , SUM(Case When val >= 500 Then 1 Else 0 End)) _500
     , CONCAT('Greater than or equal to  400: '
            , SUM(Case When val >= 400 Then 1 Else 0 End)) _400
     , CONCAT('Greater than or equal to  300: '
            , SUM(Case When val >= 300 Then 1 Else 0 End)) _300
     , CONCAT('Greater than or equal to  200: '
            , SUM(Case When val >= 200 Then 1 Else 0 End)) _200
     , CONCAT('Greater than or equal to  100: '
            , SUM(Case When val >= 100 Then 1 Else 0 End)) _100
     , CONCAT('Greater than or equal to   10: '
            , SUM(Case When val >= 10 Then 1 Else 0 End)) _10
     , CONCAT('Greater than or equal to    5: '
            , SUM(Case When val >= 5 Then 1 Else 0 End)) _5
     , CONCAT('Greater than or equal to    2: '
            , SUM(Case When val >= 2 Then 1 Else 0 End)) _2
     , CONCAT('Greater than or equal to    1: '
            , SUM(Case When val >= 1 Then 1 Else 0 End)) _1
FROM   e

SQLFiddle demo

|improve this answer|||||
  • Love the second option. I'm going to give a try and compare your methods vs Rolando's. Performance will win the points. – user2732180 May 11 '14 at 18:29
  • I like your method better than mine because it's one table scan. +1 – RolandoMySQLDBA May 14 '14 at 12:20
1

PROPOSED QUERY #1

SELECT * FROM
(SELECT 'Count 1000' hdr,SUM(val >= 1000) GroupCount FROM e UNION
SELECT 'Count  500',SUM(val >=  500) GroupCount FROM e UNION
SELECT 'Count  400',SUM(val >=  400) GroupCount FROM e UNION
SELECT 'Count  300',SUM(val >=  300) GroupCount FROM e UNION
SELECT 'Count  200',SUM(val >=  200) GroupCount FROM e UNION
SELECT 'Count  100',SUM(val >=  100) GroupCount FROM e UNION
SELECT 'Count   10',SUM(val >=   10) GroupCount FROM e UNION
SELECT 'Count    5',SUM(val >=    5) GroupCount FROM e UNION
SELECT 'Count    2',SUM(val >=    2) GroupCount FROM e UNION
SELECT 'Count    1',SUM(val >=    1) GroupCount FROM e) t
ORDER BY GroupCount DESC,hdr;

LOAD YOUR SAMPLE DATA

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS e;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE e (val INTEGER NOT NULL);
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO e(val) VALUES
    -> (1),(1),(5),(10),(100),(150),(199),(200),
    -> (250),(301),(401),(402),(500),(1000);
Query OK, 14 rows affected (0.17 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY #1 EXECUTED

mysql> SELECT * FROM
    -> (SELECT 'Count 1000' hdr,SUM(val >= 1000) GroupCount FROM e UNION
    -> SELECT 'Count  500',SUM(val >=  500) GroupCount FROM e UNION
    -> SELECT 'Count  400',SUM(val >=  400) GroupCount FROM e UNION
    -> SELECT 'Count  300',SUM(val >=  300) GroupCount FROM e UNION
    -> SELECT 'Count  200',SUM(val >=  200) GroupCount FROM e UNION
    -> SELECT 'Count  100',SUM(val >=  100) GroupCount FROM e UNION
    -> SELECT 'Count   10',SUM(val >=   10) GroupCount FROM e UNION
    -> SELECT 'Count    5',SUM(val >=    5) GroupCount FROM e UNION
    -> SELECT 'Count    2',SUM(val >=    2) GroupCount FROM e UNION
    -> SELECT 'Count    1',SUM(val >=    1) GroupCount FROM e) t
    -> ORDER BY GroupCount DESC,hdr;
+------------+------------+
| hdr        | GroupCount |
+------------+------------+
| Count    1 |         14 |
| Count    2 |         12 |
| Count    5 |         12 |
| Count   10 |         11 |
| Count  100 |         10 |
| Count  200 |          7 |
| Count  300 |          5 |
| Count  400 |          4 |
| Count  500 |          2 |
| Count 1000 |          1 |
+------------+------------+
10 rows in set (0.01 sec)

mysql>

PROPOSED QUERY #2

SELECT CONCAT('Greater than or equal to ',valrange,':',GroupCount) DesiredResults FROM
(SELECT * FROM (SELECT 1000 valrange,SUM(val >= 1000) GroupCount FROM e UNION
SELECT 500,SUM(val >=  500) GroupCount FROM e UNION
SELECT 400,SUM(val >=  400) GroupCount FROM e UNION
SELECT 300,SUM(val >=  300) GroupCount FROM e UNION
SELECT 200,SUM(val >=  200) GroupCount FROM e UNION
SELECT 100,SUM(val >=  100) GroupCount FROM e UNION
SELECT 10,SUM(val >=   10) GroupCount FROM e UNION
SELECT 5,SUM(val >=    5) GroupCount FROM e UNION
SELECT 2,SUM(val >=    2) GroupCount FROM e UNION
SELECT 1,SUM(val >=    1) GroupCount FROM e) t) q;

PROPOSED QUERY #2 EXECUTED

mysql> SELECT CONCAT('Greater than or equal to ',valrange,':',GroupCount) DesiredResult FROM
    -> (SELECT * FROM (SELECT 1000 valrange,SUM(val >= 1000) GroupCount FROM e UNION
    -> SELECT 500,SUM(val >=  500) GroupCount FROM e UNION
    -> SELECT 400,SUM(val >=  400) GroupCount FROM e UNION
    -> SELECT 300,SUM(val >=  300) GroupCount FROM e UNION
    -> SELECT 200,SUM(val >=  200) GroupCount FROM e UNION
    -> SELECT 100,SUM(val >=  100) GroupCount FROM e UNION
    -> SELECT 10,SUM(val >=   10) GroupCount FROM e UNION
    -> SELECT 5,SUM(val >=    5) GroupCount FROM e UNION
    -> SELECT 2,SUM(val >=    2) GroupCount FROM e UNION
    -> SELECT 1,SUM(val >=    1) GroupCount FROM e) t) q;
+---------------------------------+
| DesiredResult                   |
+---------------------------------+
| Greater than or equal to 1000:1 |
| Greater than or equal to 500:2  |
| Greater than or equal to 400:4  |
| Greater than or equal to 300:5  |
| Greater than or equal to 200:7  |
| Greater than or equal to 100:10 |
| Greater than or equal to 10:11  |
| Greater than or equal to 5:12   |
| Greater than or equal to 2:12   |
| Greater than or equal to 1:14   |
+---------------------------------+
10 rows in set (0.00 sec)

mysql>

Note : Proposed Query #2 has the desired output

GIVE IT A TRY !!!

|improve this answer|||||
  • Thanks Rolando! - This works well for smaller tables, but I should have mentioned I need it to work for millions of records. If I have a table of a million rows, this solution actually does a full table scan for each of the unionized segments essentially it reads 10 million rows. I was hoping to scan each row once, then bucket it in the appropriate variable. - Any thoughts on how to scan the table once? – user2732180 May 11 '14 at 5:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.