Thanx for the reply.

I tried this:

SELECT FIRST 20 SUM(V.vnd_total) as TOTAL,
(SELECT 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-1-01' AND '2006-1-31'
GROUP BY C.cli_nome
ORDER BY SUM(V.vnd_total) DESC

And i got this error:

Dynamic SQL Error SQL error code = -104 Invalid expression in the select
list (not contained in either an aggregate function or the GROUP BY clause)

Whats wrong?

On 3/29/06, Svein Erling Tysvær <svein.erling.tysvaer@...>
wrote:

>
> --- 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
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
> ------------------------------
> YAHOO! GROUPS LINKS
>
>
> - Visit your group "firebird-support<http://groups.yahoo.com/group/firebird-support>"
> on the web.
>
> - To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com<firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> - Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>
> ------------------------------
>

[Non-text portions of this message have been removed]