CODEWITHSUNDEEP
mysqldatabase
marcvander
marcvander

Reputation: 649

Get total of rows with SUM GROUP BY

I'm trying to get the total of subtotal that I get from a SUM and GROUP BY query.

Here is my query so far:

  SELECT SUM(opportunite_montant_ht)*(stade_pourcentage/100) 
      As subtotal
    FROM opportunite O 
    JOIN opportunite_stade OS 
      ON O.opportunite_stade=OS.stade_id
GROUP BY stade_id

And the result:

subtotal

35100.0000

330.0000

Now how can I get only the total of these subtotals with my query?

Upvotes: 2

Views: 51

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 176024

You could use WITH ROLLUP to get subtotal for each stade_id and grand total:

SELECT stade_id, SUM(opportunite_montant_ht * stade_pourcentage/100) as subtotal
FROM opportunite O 
JOIN opportunite_stade OS
  ON O.opportunite_stade=OS.stade_id
GROUP BY stade_id WITH ROLLUP;

Output will be like:

+----------+------------+
| stade_id |  subtotal  |
+----------+------------+
| 1        | 35100.0000 |
| 2        |   330.0000 |
| NULL     | 35430.0000 |
+----------+------------+

EDIT:

Indeed this works, though I was trying to get only the total, and not all the subtotals

Then use the simplest solution possible - remove GROUP BY:

SELECT SUM(opportunite_montant_ht*stade_pourcentage/100) AS total
FROM opportunite O JOIN opportunite_stade OS ON O.opportunite_stade=OS.stade_id

Upvotes: 1

Jens
Jens

Reputation: 69470

This should work:

select sum(subtotal) from (SELECT SUM(opportunite_montant_ht)*(stade_pourcentage/100) as subtotal
FROM opportunite O JOIN opportunite_stade OS ON O.opportunite_stade=OS.stade_id
GROUP BY stade_id) as query

Upvotes: 2

Matt
Matt

Reputation: 15061

Use an additional SUM.

SELECT SUM(SUM(opportunite_montant_ht)*(stade_pourcentage/100)) AS subtotal
FROM opportunite O 
JOIN opportunite_stade OS ON O.opportunite_stade= OS.stade_id
GROUP BY stade_id

OR:

SELECT SUM(SELECT SUM(opportunite_montant_ht)*(stade_pourcentage/100) as subtotal
FROM opportunite O JOIN opportunite_stade OS ON O.opportunite_stade=OS.stade_id
GROUP BY stade_id)
FROM dual

Upvotes: 0

Related Questions