Get the total of all group by

1

Do you know how I can get the total of a GROUP BY ? Let me explain: a grouping is made per plan and each plan carries its total; I need to get the total of all that.

An example is

(plan) (cantidad usuarios no admin) costo plan   total
admin      5                            4         (5*4 = 20)
silver     7                            5         (7*5 = 35)
gold       20                           10        (10*20 = 200)

----------------------------------------------------------------------
                                            gran total =    255     

Try ROLLUP BUT it does not work. it gives me an erroneous result

The grand total is what I want to obtain and now if you see that you already know that it is because each one has a different cost. What I need is the query that gets great total.

    
asked by santiago 06.06.2017 в 06:21
source

2 answers

2

I recommend using a SELECT nested, since it allows you to separate the operations.

For example, consider the following simple table called planes :

+----+--------+----------+-------+
| id | plan   | usuarios | costo |
+----+--------+----------+-------+
| 1  | admin  | 2        | 4     |
| 2  | admin  | 3        | 4     |
| 3  | silver | 1        | 5     |
| 4  | silver | 2        | 5     |
| 5  | silver | 4        | 5     |
| 6  | gold   | 5        | 10    |
| 7  | gold   | 8        | 10    |
| 8  | gold   | 4        | 10    |
| 9  | gold   | 3        | 10    |
+----+--------+----------+-------+

To get only the grand total, the query would be like this:

SELECT
    SUM(total) AS total
FROM 
    (
        SELECT
            plan,
            (SUM(usuarios) * MAX(costo)) AS total
        FROM
            planes
        GROUP BY
            plan
    ) planes;

Result

+--------+
| total  |
+--------+
| 255    |
+--------+

If you want to obtain such a table (almost) as the samples in your example, the query would be:

SELECT
    plan,
    SUM(usuarios) AS total_usuarios,
    MAX(costo) AS total_costo,
    SUM(total) AS total
FROM
    (
        SELECT
            plan,
            usuarios,
            costo,
            (usuarios * costo) AS total
        FROM
            planes
    ) planes
GROUP BY
    plan
WITH ROLLUP;

Result:

+--------+----------------+-------------+-------+
| plan   | total_usuarios | total_costo | total |
+--------+----------------+-------------+-------+
| admin  | 5              | 4           | 20    |
| gold   | 20             | 10          | 200   |
| silver | 7              | 5           | 35    | 
| NULL   | 32             | 19          | 255   |
+--------+----------------+-------------+-------+
    
answered by 06.06.2017 / 14:44
source
2

This solution could be

select 
    sum(total) gran_total
from (
    select 
        plan,
        cantidad_usuarios,
        costo_plan,
        sum(cantidad_usuarios * costo_plan) total
    from
    tabla_movimiento
    group by plan
) consulta

but I do not know why it would be necessary to go through a group by if you want only the grand total, the sql could be simpler:

    select 
        sum(cantidad_usuarios * costo_plan) gran_total
    from
    tabla_movimiento

you can also make a combination between both queries:

    select 
        plan,
        cantidad_usuarios,
        costo_plan,
        sum(cantidad_usuarios * costo_plan) total
    from
    tabla_movimiento
    group by plan

    union all


    select 
        null plan,
        null cantidad_usuarios,
        null costo_plan,
        sum(cantidad_usuarios * costo_plan) total
    from
    tabla_movimiento

to get a result like this:

    
answered by 06.06.2017 в 16:12