MySQL Query: 2 records with different count totals possible to combine when using COALESCE.. WITH ROLLUP?

Question

I'm currently developing a MySQL query that counts different totals of people who bet in certain categories. Say Pro Baseball, Flex, World Cup, ULeague. The thing is Flex and World Cup is counted as one.

My SQL code so far

SELECT COALESCE(`category_desc`,'Total') AS ALLGAMES, 
COUNT(DISTINCT `player_id`) AS 'No. of Person',COUNT(`bet_id`) AS 'No. of Bets' FROM `bet`
WHERE `category_desc` IN ('World Cup','Flexi','ULeague','Pro Baseball') 
GROUP BY ALLGAMES WITH ROLLUP

Result:

+----------+---------------+-----------+
| ALLGAMES | No. of Persons| Bet Count |
+----------+---------------+-----------+
|Flexi     | 723           |    100,100|
|ProBasebal| 247           |   400,000 |
|World Cup | 709           |    20,375 |
|ULeague   | 1000          |     5,311 |
+----------+---------------+-----------+

Is the following result possible thru sql statement?

+----------+---------------+-----------+
| ALLGAMES | No. of Persons| Bet Count |
+----------+---------------+-----------+
|Flexi/WCup| 1432          |    120,475|
|ProBasebal| 247           |   400,000 |
|ULeague   | 1000          |     5,311 |
+----------+---------------+-----------+

Say We'd like to combine the data of Flexi and World Cup.

And Also is it possible to arrange the games by custom order?

Like ULeague comes first, next would be ProBasebal.

Can we assume you want the rolled up line to say "Total"? Can you create an [SQLFiddle](http://sqlfiddle.com) with some sample data? — Bohemian, Mar 08 '15 at 04:33
@hikki Just curious if you had a chance to try my query and if it worked? — jpw, Mar 08 '15 at 22:23

jpw · Answer 1 · 2015-03-16T10:25:29.483

This should work, although it can surely be improved.

SELECT 
    COALESCE(allgames, 'Total') AS ALLGAMES,
    player_count AS 'No. of Person',
    bet_count AS 'No. of Bets'
FROM
    (SELECT 
        CASE
            WHEN `category_desc` in ('Flexi' , 'World Cup') THEN 'Flexi/WCup'
            ELSE `category_desc`
        END AS ALLGAMES,
        COUNT(DISTINCT `player_id`) player_count,
        COUNT(`bet_id`) bet_count,
        CASE `category_desc`
            WHEN 'ULeague' THEN 1
            WHEN 'Pro Baseball' THEN 2
            WHEN 'World Cup' THEN 3
            WHEN 'Flexi' THEN 3
        END AS sort
    FROM
        `bet`
    WHERE
        `category_desc` IN ('World Cup' , 'Flexi', 'ULeague', 'Pro Baseball')
    GROUP BY ALLGAMES WITH ROLLUP) a
ORDER BY (allgames <> 'Total') DESC, sort;

Sample SQL Fiddle

Sample output:

|     ALLGAMES | No_of_Persons | Bet_count |
|--------------|---------------|-----------|
|      ULeague |             1 |         4 |
| Pro Baseball |             1 |         4 |
|   Flexi/WCup |             2 |         8 |
|        Total |             4 |        16 |

Bohemian · Accepted Answer · 2015-03-18T16:01:03.140

1

You just need to convert category_desc Flex and World Cup to Flex/WCup, otherwise use the value as-is:

SELECT COALESCE(ALLGAMES, 'Total') AS ALLGAMES, No_of_Person, No_of_Bets FROM (
SELECT
  if(category_desc in ('Flexi', 'World Cup'), 'Flexi/WCup', category_desc) AS ALLGAMES,
  COUNT(DISTINCT player_id) AS No_of_Person,
  COUNT(bet_id) AS No_of_Bets
FROM bet
WHERE category_desc IN ('World Cup','Flexi','ULeague','Pro Baseball') 
GROUP BY ALLGAMES WITH ROLLUP) x
ORDER BY FIND_IN_SET(COALESCE(ALLGAMES, 'Total'), 'Flexi/WCup,ProBaseball,ULeague,Total')

See SQLFiddle

edited Mar 18 '15 at 16:01

answered Mar 08 '15 at 03:47

Bohemian

412,405
93
575
722

Hi Sir, your solution showed my expected results. Thank you I also figured out on how to have multiple ifs in this way. Thank you – leipzy Mar 08 '15 at 08:54
@hikki non probkem. I added a custom order by as per question. – Bohemian Mar 08 '15 at 09:53
@Bohemian Using an `order by` after the `group by` did not work for my using MySQL 5.6.17 but returned the error _Error Code: 1221. Incorrect usage of CUBE/ROLLUP and ORDER BY_ which was the reason I wrapped my query in an outer to do the sorting. What version did you make it work on if I may ask? – jpw Mar 08 '15 at 11:57
@Bohemian Btw, SQL Fiddle threw the same error in my test: http://www.sqlfiddle.com/#!9/2bbb6/5 – jpw Mar 08 '15 at 12:04
@jpw I didn't test the query, I just typed it in. At the moment I (on an iPhone) can't use SQLFiddle to test, but I've edited the query to use an outer query for the ordering and moved the coalesce to the outer query. – Bohemian Mar 08 '15 at 21:25
How to make FIND_IN_SET() work? I'm having an error "#1582 - Incorrect parameter count in the call to native function 'FIND_IN_SET' " – leipzy Mar 10 '15 at 09:14
@hikki sorry - got the syntax wrong. Try it now (see edited answer). (btw, SQLFiddle still down, so can't check if the whole thing works) – Bohemian Mar 10 '15 at 16:54
Still not working for me. Also what is the "GROUP BY ALLGAMES WITH ROLLUP) x ORDER BY 1 - FIND_IN_SET(ALLGAMES, 'Flexi/WCup,ProBasebal,ULeague')" part? is x really part of syntax? – leipzy Mar 11 '15 at 05:37
@hikki yes "x" is part of the syntax - you must give an alias to a subquery (even if you don't use it) - I usually use "x". SQLFiddle is *still* down, so I can't test and post a link. I'll try later to verify the query – Bohemian Mar 11 '15 at 10:39
I dont got errors now but the 'Total' word is now on the topmost of the categories.. I tried to make it show in the bottom by this code.. FIND_IN_SET(ALLGAMES, 'Flexi/WCup,ProBasebal,ULeague,Total') but no change – leipzy Mar 12 '15 at 02:43
Also tried FIELD() function described here http://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql but no luck – leipzy Mar 12 '15 at 03:50
If you want to control the ordering for *all* categories, remove the `1 -` from the order by; ie use `ORDER BY FIND_IN_SET(ALLGAMES, 'Flexi/WCup,ProBasebal,ULeague,Total')`, however if there's a category *not* listed, it will appear *first* in the output. – Bohemian Mar 13 '15 at 02:01
I removed the 1 - but still the Total appears first. My query now is SELECT COALESCE(ALLGAMES, 'Total') AS ALLGAMES, No_of_Person, No_of_Bets FROM ( SELECT if(category_desc in ('Flexi', 'World Cup'), 'Flexi/WCup', category_desc) AS ALLGAMES, COUNT(DISTINCT `player_id`) AS 'No_of_Person', COUNT(`bet_id`) AS 'No_of_Bets' FROM `bet` WHERE `bet_id`>0 GROUP BY ALLGAMES WITH ROLLUP) q ORDER BY FIND_IN_SET(ALLGAMES, 'Flexi/WCup,ProBasebal,ULeague'). – leipzy Mar 15 '15 at 04:19
I don't see "Total" in list. I told you that if you order by find_in_set then unlisted values will order first. If you use 1 - find_in_set unlisted values will order last, but you must list your values in reverse order. Alternatively you can use DESC instead of 1 -, but the effect is the same – Bohemian Mar 15 '15 at 07:59
I tried also ORDER BY FIND_IN_SET(ALLGAMES, 'Flexi/WCup,ProBasebal,ULeague,TOTAL') even ORDER BY FIND_IN_SET(ALLGAMES, 'TOTAL,Flexi/WCup,ProBasebal,ULeague,') but the effect is still the same – leipzy Mar 18 '15 at 09:03
@hikki Two problems: Firstly it's "Total", not "TOTAL", but the main problem is you need to use `COALESCE(ALLGAMES, 'Total')` in `FIND_IN_SET`, not just `ALLGAMES`, which is null for the rolled up row, and a null passed to a function results in another null, and nulls order first. Click the link to see the edited query executing correctly. If you want to avoid recoding the `COALESCE` in the `FIND_IN_SET` you would need to wrap the query in a further outer query so you'reworking with"Total" and not a null/ – Bohemian Mar 18 '15 at 16:06

score 0 · Answer 3 · answered Mar 08 '15 at 02:39

Both of your requests (Customer sort order, and combination of two Categories) is achievable with CASE Statements.

SELECT 
    CASE 
        WHEN `category_desc` IN ('World Cup', 'Flexi')
            THEN 1
        WHEN `Uleague` 
            THEN 2
        WHEN `Pro Baseball`
            THEN 3
        END AS SORTORDER
    CASE 
        WHEN `category_desc` IN ('World Cup', 'Flexi')
            THEN 'Flexi/WCup'
        ELSE
            COALESCE(`category_desc`, 'Total') 
        END AS ALLGAMES,
    COUNT(DISTINCT `player_id`) AS 'No. of Person',
    COUNT(`bet_id`) AS 'No. of Bets'
FROM `bet`
WHERE `category_desc` IN (
        'World Cup',
        'Flexi',
        'ULeague',
        'Pro Baseball'
        )
GROUP BY SORTORDER, ALLGAMES
WITH ROLLUP
ORDER BY SORTORDER;

Using this logic you can make whatever type of sort order you wish using any combination of your fields. You can also combine any of your categories to make a multi-category row in your recordset.

MySQL Query: 2 records with different count totals possible to combine when using COALESCE.. WITH ROLLUP?

3 Answers3