SQLTeam.com | Weblogs | Forums

Sum of 2 sums

mysql

#1

Hi there.
Please I need help with Sum of 2 sum numbers.
Well I have table where showing user sum numbers.
At end of table I need to make row which showing sum of all numbers.
Table: photo
So on blue row I need sum of all numbers up.
My code: $groupQuery = "SELECT r.broj_rac AS racun, COUNT(stavka) AS stavka, d.naziv1 AS dobavljac, SUM(iznos_osnovica) AS osnovica, SUM(iznos_porez) AS porez, SUM(moze_se_odbiti) AS odbiti, SUM(nemoze_se_odbiti) AS neodbiti, SUM(neoporezivo) AS neoporezivo, SUM(ukupno) AS ukupno1, r.sveukupno AS sveukupno FROM racuni AS r LEFT JOIN dobavljaci AS d ON d.sifra = r.dobavljac WHERE partner = '".$_SESSION['sifra_partnera']."' GROUP by broj_rac";
Is it possible to make something like SUM(SUM(ukupno AS ukupno1) AS ukupno2) etc..
please help
Thanks !


#2

Wrap your query in an outer query that returns the same columns but groups and sums to get ukupno2


#3

Add "WITH ROLLUP" to the GROUP BY: the sum'med values will have a grand total, the non-sum'med values will have NULL in them:

"SELECT r.broj_rac AS racun, COUNT(stavka) AS stavka, d.naziv1 AS dobavljac, SUM(iznos_osnovica) AS osnovica, SUM(iznos_porez) AS porez, SUM(moze_se_odbiti) AS odbiti, SUM(nemoze_se_odbiti) AS neodbiti, SUM(neoporezivo) AS neoporezivo, SUM(ukupno) AS ukupno1, r.sveukupno AS sveukupno FROM racuni AS r
LEFT JOIN dobavljaci AS d
ON d.sifra = r.dobavljac
WHERE partner = '".$_SESSION['sifra_partnera']."'
GROUP by broj_rac WITH ROLLUP";


#4

help please


#5

We've given you two alternatives. Have you tried them?


#6

I did it with jquery because someone told me to do not make long query's because sql is not calculator. Later it can broke it.. Is that true?


#7

SQL is actually very good at calculations. Especially aggregations. I would be so bold as to claim that doing it in SQL will likely be faster and consume far less resources than using an application-level solution (which JQuery is)


#8

SQL does have some issues with properly computing computations on decimal columns, that is, SUM(cola * colb * colc).

But a simple sum of a single column, such as SUM(cola), should be fine.


#9
  1. create a stored procedure and call that instead of concatenated string of DML
  2. depending on how often your data changes you could cache your data in something like redis

Do you have an rest api your application is calling? I would highly recommend that. then you can even cache your whole route ie
this is express.js example.

app.get(api + 'customers', getCustomers, cache.route('home'));