--- In firebird-support@yahoogroups.com, "Fabio Gomes" wrote:

> Hi guys :)

Hi Fabio!

> I making a little report here, and i need to know the 20 clients
> that bought more in value and in quantity, so i came up with this
> select:
>
> SELECT FIRST 20 SUM(V.vnd_total) as TOTAL, SUM(I.item_qtde) AS QTDE,
> C.cli_nome
> FROM VENDAS V
> JOIN items_venda I ON (I.vnd_cod=V.vnd_cod)
> JOIN clientes C ON (C.cli_cod=V.cli_cod)
> WHERE vnd_data BETWEEN '2006-01-01' AND '2006-01-31'
> GROUP BY C.cli_nome
> ORDER BY SUM(V.vnd_total) DESC

That's a decent start.

> It works as expected, with just one little problem.
>
> My table items_venda, is a table that has the items of my sellings..
> so for each row in the vendas table i have several rows in the
> items, one for each item, and when i run the query it sum the
> V.vnd_total field once for each item, but it should sum the
> V.vnd_total just once, and then sum the items.

Well, then you have to tell it to do just that, what you've done is to
work with the combined set of your three tables.

> Is there a way to make it sum just once? using a subselect or
> something like it?

Yes, I think that is what you want. Here's my guess:

SELECT FIRST 20 SUM(V.vnd_total) as TOTAL,
(SUM(I.item_qtde)
FROM items_venda I
WHERE I.vnd_cod=V.vnd_cod) AS QTDE,
C.cli_nome
FROM VENDAS V
JOIN clientes C ON (C.cli_cod=V.cli_cod)
WHERE V.vnd_data BETWEEN '2006-01-01' AND '2006-01-31'
GROUP BY C.cli_nome
ORDER BY SUM(V.vnd_total) DESC

and then, if you want to return only records that exist in items_venda
(maybe your data has this restriction anyway), add

WHERE EXISTS(SELECT * FROM items_venda I2
WHERE I2.vnd_cod=V.vnd_cod).

> And another weird stuff is.. if i use ASC in ORDER BY, it orders
> from the lowest value to the highest.. kinda weird.

ASC means ascending and that means starting from the lowest value. It
is approximately as strange as the English language ;o)

> I dont know if i make myself clear my english vocabulary is kinda
> poor and i cant find a more clear way to explain this.
>
> Thanx in advance for any help.

Hope This Helps,
Set