Is AlaSql Support GROUPING function?

Question

*Sorry,I made some mistakes,and crude manners.

Now I trying to make Sales Summary Report with AlaSql, heres my code:

     var testData = [
    { Area: "Area 1", Shop: "Shop 1", Val: 5 },
    { Area: "Area 1", Shop: "Shop 2", Val: 20 },
    { Area: "Area 2", Shop: "Shop 1", Val: 25 },
    { Area: "Area 2", Shop: "Shop 2", Val: 40 }
    ];
    
    
    res = alasql( 'SELECT CASE WHEN \
    GROUPING(Area) = 1 THEN "ALL"  ELSE MAX(Area) END AS Area,  \
    CASE WHEN GROUPING(Area) = 1 THEN "TOTAL" \
    GROUPING(Shop) = 1 THEN "SUBTOTAL" ELSE MAX(Shop) END AS Shop,\
    SUM(Val) AS Val  \
    INTO HTML("#res1",{headers:true}) FROM ? \
    GROUP BY ROLLUP(Area,Shop)', [testData]);

table {border:1px solid black}

<script src="https://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>
<p>ROLLUPTEST()</p><div id="res1"></div>

I expect to show the table with summary of value.

{ Area: "Area 1", Shop: "Shop 1"  , Val: 5 },
{ Area: "Area 1", Shop: "Shop 2"  , Val: 20 },
{ Area: "Area 1", Shop: "SUBTOTAL", Val: 25 },
{ Area: "Area 2", Shop: "Shop 1"  , Val: 25 },
{ Area: "Area 2", Shop: "Shop 2"  , Val: 40 },
{ Area: "Area 2", Shop: "SUBTOTAL", Val: 65 },
{ Area: "ALL"   , Shop: "TOTAL"   , Val: 90 },

But When Run it, Debugger shows "Script error." and no table shows in Browser.

I guess AlaSql do not support GROUPING Function. If anyone knows about this matter,Please tell me it.

Answer 1

No, AlaSQL knows the GROUPING keyword only in another context, i.e. in GROUPING SETS:

GROUP BY GROUPING SETS(Area, Shop)

If your Area and Shop fields are non-null, then you can work around it as follows:

SELECT   CASE WHEN Area IS NULL THEN "ALL" ELSE Area END AS Area, 
         CASE WHEN Shop IS NULL THEN 
            CASE WHEN Area IS NULL THEN "TOTAL" ELSE "SUBTOTAL" END 
            ELSE Shop
         END as Shop,
         SUM(Val) AS Val 
INTO     HTML("#res1", {headers:true}) 
FROM     ?
GROUP BY ROLLUP(Area,Shop)