How to group and count

How to group and count?

Source: StackOverflow.com

Question

There is an array like this:

    [{ dep: 'A', qt: 10, price: 5},
     { dep: 'A', qt: 5,  price: 2.30 },
     { dep: 'B', qt: 3,  price: 2.20 },
     { dep: 'C', qt: 1,  price: 4 },
     { dep: 'C', qt: 4,  price: 10 }
     ...etc.. 
    ]

What's the elegant way to both group and sum values, resulting into:

    [{ dep: 'A', qt: 15, price: 61.5 },
     { dep: 'B', qt: 3, price: 2.20 },
     { dep: 'C', qt: 5: price: 44 }
    ]

Answer

You can do it in elegant way:

    var res = alasql('SELECT dep, SUM(qt) AS qt, SUM(qt*price) AS amt, 
                        AGGR(amt/qt) AS price FROM ? GROUP BY dep',[data]); 

Try this example at jsFiddle.

I changed a little bit the algorithm for calculation of average price based on quantity sold.