MySql SUM (subquery)

Question

I'm trying to select the sum of a mono-field subquery but I can't find any good syntax.

Solutions proposed here won't works for me because my subquery needs a GROUP BY to avoid duplicate entries.

Here is the subquery (simplified cause it's long):

SELECT montant FROM table1 
JOIN flag ON table1.flag = flag.id
JOIN historique ON historique.bu_id = flag.bu_id 
WHERE historique.com_id = '144'
AND table1.date > '$aDate' AND table1.date_end < '$anotherDate'
GROUP BY table1.id

What I want is:

SELECT SUM(Subquery) AS Total

But it does not works as is, and if I do not use the GROUP BY statement it counts table1.montant multiple times

EDIT: Here is a screen capture of the complete subquery with a COUNT(id) to show you what I mean:

**Please** include sample data, table definitions and expected output in your question — M Khalid Junaid, Jun 26 '18 at 10:04
So you are trying to sum all returned values? What's wrong with `SELECT SUM(montant)...`? — revo, Jun 26 '18 at 10:05
I'm trying to sum all results of subquery. The group by is here cause the JOINs makes the subquery return multiple times each "montant", so the SUM would be wrong. — JM445, Jun 26 '18 at 10:09

score 0 · Answer 1 · answered Jun 26 '18 at 10:07

if you need a global sum you should use just

SELECT sum(montant) FROM table1 
JOIN flag ON table1.flag = flag.id
JOIN historique ON historique.bu_id = flag.bu_id 
WHERE historique.com_id = '144'
AND table1.date > '$aDate' AND table1.date_end < '$anotherDate'

avoiding group by

score 0 · Answer 2 · answered Jun 26 '18 at 10:23

0

I found the problem: My JOINs conditions where not good, I needed to add a comparison between table1 and historique. Now there are no duplicated rows

answered Jun 26 '18 at 10:23

JM445

168
1
12

MySql SUM (subquery)

2 Answers2