SELECT
SUM(c.vlrbase) AS vlrloja,
l.cod_loja,
l.nome_loj,
(SELECT
SUM(c.vlrbase) AS vlrloja1
FROM
tab_comissao AS c, tab_prot AS p, tab_lojas AS l
WHERE c.datalanc>=’2011-01-04′
AND c.datalanc=’2011-01-04′
AND c.datalanc=’2011-01-04′
AND c.datalanc<='2011-02-02'
GROUP BY l.cod_loja
ORDER BY vlrloja DESC

This gives me the amount, the total amount and the percentage of each row… sorry for being a newbie in this case.

BTW, “WHERE c.ade = p.ade AND p.cod_loja = l.cod_loja” is redundant: you already use these conditions on the JOIN clauses.

SELECT
l.cod_loja,
l.nome_loj,
SUM(c.vlrbase) AS soma
FROM
tab_comissao c INNER JOIN tab_prot p ON (c.ade=p.ade)
INNER JOIN tab_lojas l ON (l.cod_loja=p.cod_loja)
WHERE c.ade = p.ade AND p.cod_loja = l.cod_loja
AND c.datalanc>=’2011-01-04′
AND c.datalanc<='2011-02-02'
GROUP BY l.cod_loja
ORDER BY soma DESC

Where gives me this result:

3 store-3 345874.54
7 store-7 213022.86
6 store-6 209934.94
…
…
…
57 store-57 2827.18

So, how could I do this in order to show me the correct ranking?

Thanks in advance,

Kleyber Derick

PS: I put this question on Roland Bouman's blog too.

Browsing through High Performance MySQL (2nd Ed.), I suddenly notice this has all been discussed at length, at the end of chapter 4.
Seems like I’ve mostly repeated things that have been said there; I apparently offer little new.

Shlomi

Thank you. I will make an effort to get more reliable information.
I think we should avoid the situation where we say “hmmm.. we’re not sure about this so let’s not use it”, but push towards a reliable answer.

Shlomi

Nice trick. I’m not entirely convinced though, but it looks promising. I’ll do my best to try and falsify the result (:p) not to bug you of course, but to achieve more certainty that this is indeed reliable.

Thanks so far 🙂