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.

Just for the record, your `testData` is not a valid object array....can you fix the `Task: Val: x` part? — Hackerman, Aug 28 '17 at 14:35
Welcome to Stack Overflow! Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a [mcve]. Use the "edit" link to improve your *question* - do not add more information via comments. Thanks! — GhostCat, Aug 28 '17 at 14:42

score 0 · Answer 1 · answered Aug 28 '17 at 17:07

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)

Is AlaSql Support GROUPING function?

1 Answers1