Wsrtjtyk

My source table (wplott_wpkl_winner) contains the field "lottery_number" that carries 1 to 6 digit numbers and the corresponding "draw_date".

    lottery_number | draw_date

==================================

    0024           | 2018-11-10

    4456           | 2018-11-10

    3895           | 2018-11-10

    4557           | 2018-11-10

    4225           | 2018-11-10

    2896           | 2018-11-10

    3354           | 2018-11-10

    1895           | 2018-11-10

    78466          | 2018-11-10

    998556         | 2018-11-10

My current MYSQL query is as below (I am trying to group the data into ranges)

select

        count(case when wplott_wpkl_winner.lottery_number between 0 and 999 then 1 end) `0-999`,

        count(case when wplott_wpkl_winner.lottery_number between 1000 and 1999 then 1 end) `1000-1999`,

        count(case when wplott_wpkl_winner.lottery_number between 2000 and 2999 then 1 end) `2000-2999`,

        count(case when wplott_wpkl_winner.lottery_number between 3000 and 3999 then 1 end) `3000-3999`,

        count(case when wplott_wpkl_winner.lottery_number between 4000 and 4999 then 1 end) `4000-4999`,

        count(case when wplott_wpkl_winner.lottery_number between 5000 and 5999 then 1 end) `5000-5999`,

        count(case when wplott_wpkl_winner.lottery_number between 6000 and 6999 then 1 end) `6000-6999`,

        count(case when wplott_wpkl_winner.lottery_number between 7000 and 7999 then 1 end) `7000-7999`,

        count(case when wplott_wpkl_winner.lottery_number between 8000 and 8999 then 1 end) `8000-8999`,

        count(case when wplott_wpkl_winner.lottery_number between 9000 and 9999 then 1 end) `9000-9999`

    from wplott_wpkl_winner

    where CHAR_LENGTH(wplott_wpkl_winner.lottery_number) = 4 AND wplott_wpkl_winner.draw_date > '2013-06-30'

It provides the below output

    0-999 | 1000-1999 | 2000-2999 | 3000-3999 | 4000- 4999 .... etc

=====================================================================

    1     | 1         | 1         | 2         | 3

However, I would like to get the output in the below format.

    Range     | Count

=======================

    0-999     | 1

    1000-1999 | 1

    2000-2999 | 1

    3000-3999 | 2

    4000-4999 | 3

    .

    .

    .

Any help is highly appreciated. I did search in SO for a similar answer but none of the answers helped my particular case.

Thanks in advance!

One approach uses a series of unions:

SELECT

    `range`,

    count

FROM

(

    SELECT 1 AS pos, '0-999' AS `range`, COUNT(*) AS count

    FROM wplott_wpkl_winner

    WHERE draw_date > '2013-06-30' AND lottery_number BETWEEN 0 AND 999

    UNION ALL

    SELECT 2, '1000-1999', COUNT(*)

    FROM wplott_wpkl_winner

    WHERE draw_date > '2013-06-30' AND lottery_number BETWEEN 1000 AND 1999

    UNION ALL

    ...      -- fill in remaining ranges here

) t

ORDER BY pos;

Note that I introduce a computed column pos so that we may maintain the desired ordering of the ranges in the final output. Also, I removed the check on the CHAR_LENGTH of the lottery_number, since the conditional sums already handle this logic.