Thanx, it was very simple ^^

Anyway, now i have another problem, my query is working, but i need to have
an option to order by the Item_qtde and vnd_total, with vnd_total it works
fine, but with item_qtde i got 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-03-01' AND '2006-03-31'
GROUP BY C.cli_nome, V.vnd_cod
ORDER BY SUM(I.item_qtde) DESC

Dynamic SQL Error SQL error code = -206 Column unknown I.ITEM_QTDE At line
10, column 21.

I tried ORDER BY QTDE, but it didnt work, is there any way to do this?

thanx for any help :)

On 3/29/06, Arno Brinkman <fbsupport@...> wrote:
>
> Hi,
>
>
> > 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?
>
> V.vnd_cod isn't part of the GROUP BY clause.
> Thus include V.vnd_cod in the GROUP BY clause or SUM the sub-select.
>
> Regards,
> Arno Brinkman
> ABVisie
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> General database development support:
> http://www.databasedevelopmentforum.com
>
> Firebird open source database (based on IB-OE) with many SQL-99 features :
> http://www.firebirdsql.org
> http://www.firebirdsql.info
> http://www.fingerbird.de/
> http://www.comunidade-firebird.org/
>
> Support list for Firebird and Interbase users :
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep :
> news://newsgroups.firebirdsql.info
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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]