> SELECT N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome, SUM(...
> GROUP BY N.vnd_cod

Every column not an aggregate column (e.g. SUM or MAX) must be in the
GROUP BY clause, i.e. you must use

SELECT N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome, SUM(...
GROUP BY N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome

I don't think there is anything wrong with your use of AS,
Set

--- In firebird-support@yahoogroups.com, Fabio Gomes wrote:
>
> Hi, i tried to make the select, but it isnt working :(
>
> Here is my select:
>
> SELECT
> N.vnd_data,
> N.vnd_desconto,
> N.vnd_total,
> C.cli_nome,
> SUM((COALESCE(P.pro_com_porcento,V.ved_comissao) /100) *
(I.item_qtde *
> I.item_valor)),
> SUM(COALESCE((P.pro_comissao * I.item_qtde), 0) AS valor
> FROM items_venda I
> JOIN vendas N ON (N.vnd_cod=I.vnd_cod)
> JOIN clientes C ON (N.cli_cod=C.cli_cod)
> JOIN vendedores V ON (N.ved_cod=V.ved_cod)
> JOIN produtos P ON (I.pro_cod=P.pro_cod)
> WHERE N.ved_cod='2' AND (N.vnd_data >= '02/02/2006')
> GROUP BY N.vnd_cod
> ORDER BY N.vnd_data ASC, N.vnd_cod ASC
>
> Its giving me an error in the AS, i dont know if i can use AS in the SUM
> function.. then i tried this:
>
> SELECT N.vnd_data, N.vnd_desconto, N.vnd_total, C.cli_nome,
> SUM(
> COALESCE(
> SUM((COALESCE(
> P.pro_com_porcento,V.ved_comissao) /100) * (I.item_qtde *
I.item_valor)),
>
SUM(COALESCE((P.pro_comissao *
> I.item_qtde), 0))
> )
> )
> FROM items_venda I
> JOIN vendas N ON (
> N.vnd_cod=I.vnd_cod)
> JOIN clientes C ON (
> N.cli_cod=C.cli_cod)
> JOIN vendedores V ON (
> N.ved_cod=V.ved_cod)
> JOIN produtos P ON (
> I.pro_cod=P.pro_cod)
> WHERE ved_cod='2'
> GROUP BY N.vnd_cod
> ORDER BY N.vnd_data ASC,
N.vnd_cod ASC
>
> this give me:
>
> 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)
>
> Can anyone help me with this stuff?
>
> Thanks in advance,
>
> Fábio.
>
> On 2/7/06, Fabio Gomes <fabioxgn@...> wrote:
> >
> > Hi Alexandre, thanx for your reply.
> >
> > I understood your select and i ll try to adapt for what i have:
> >
> > i have 3 commission types:
> >
> > 1 - the vendor commission percent
> >
> > 2 - the product commission value (that is a fixed value for each
product
> > that is sold)
> >
> > 3 - the product commission percent value
> >
> > my order is something like the following:
> >
> > if the product commission value isnt null, it takes this value
instead of
> > the product commission percent, and if the commission percent is
null too,
> > then it will use the vendor percent..
> >
> > But thanx, i think i already know how to do it :)
> >
> > On 2/6/06, Alexandre Benson Smith <iblist@... > wrote:
> > >
> > > Hi Fabio,
> > >
> > > Fabio Gomes wrote:
> > >
> > > >Hi all, i m new to firebird, so i don't know exactly how i can
make a
> > > >procedure or something like it to solve my problem.
> > > >
> > > >I ll try to be as clear as my english allow me to, but sorry, my
> > > english is
> > > >kinda bad.
> > > >
> > > >I have a vending control, and the vendors have a fixed
commission value
> > > is
> > > >used to calculate the commission value, my table structure is like
> > > this:
> > > >
> > > >vending:
> > > >vendor_id
> > > >total
> > > >
> > > >vendors:
> > > >vendor_id
> > > >vendor_comission
> > > >vendor_name
> > > >
> > > >items:
> > > >product_id
> > > >vending_id
> > > >
> > > >products:
> > > >product_id
> > > >product_comission_value
> > > >product_comission_percent
> > > >
> > > >Actually my report calculate the commission value based on the
vending
> > > >total, but i need to make it calculate like this:
> > > >
> > > >if i have a product_comission_value, it will forget the
> > > vendor_comission and
> > > >add this value to the total commission..
> > > >
> > > >So basically it have to select the vendings from that vendor,
and for
> > > each
> > > >product test if the product have a commission value, if it has
so it
> > > >calculates it, if it doesn't the vendors commission is used.
> > > >
> > > >
> > > I'd go with a simple select liek this:
> > >
> > > The comission is a fixed value ? a percent over the total sale ?
I am
> > > missing the Value of each item, you have the Value of the whole
sale.
> > >
> > > select
> > > V.Vendor_Name, Sum(Coalesce(P.Product_Comission_Value,
> > > V.Vendor_Comission) * I.Value/100)
> > > from
> > > Items I join
> > > Products P on (P.ProdutoID = I.ProductID) join
> > > Vending V2 on (V2.Vending_ID = I.Vending_ID) join
> > > Vendors V on (V.Vendor_ID = V2.Vednor_ID)
> > > group by
> > > V.Vendor_Name
> > >
> > > It could have some miswritten table name or column name, but the
point
> > > that solves your problem is that one:
> > > Sum(Coalesce(P.Product_Comission_Value, V.Vendor_Comission) *
I.Value
> > > /100)
> > >
> > > Coalesce returns the first argument if it is not null or the second
> > > otherwise.
> > >
> > > As you said:
> > > "test if the product have a commission value, if it has so it
calculates
> > >
> > > it, if it doesn't the vendors commission is used."
> > >
> > > I assume that if the product has any value it should be used on the
> > > comission calculation, otherwise (if it is null) use the
> > > vendor.comission
> > >
> > > I don't know if
> > > P.Product_Comission_Value and V.Vendor_Comission are real
(absolute and
> > > static) values or a percenth of the total, I assume it is a
percent, but
> > >
> > > you get the idea.
> > >
> > > >is it clear?
> > > >
> > > >How do you guys think its the best way to do it? with a
procedure? with
> > > >normal selects and coding by hand? i m kinda lost with this stuff.
> > > >
> > > >Thanks in advance,
> > > >
> > > >Fábio.
> > > >
> > > >
> > >
> > > see you !
> > >
> > > --
> > > Alexandre Benson Smith
> > > Development
> > > THOR Software e Comercial Ltda
> > > Santo Andre - Sao Paulo - Brazil
> > > www.thorsoftware.com.br
> > >
> > >
> > >
> > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >
> > > 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
> > >
> > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >
> > >
> > >
> > >
> > > SPONSORED LINKS
> > > Technical
support<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw>
Computer
> > > technical
support<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA>
Compaq
> > > computer technical
support<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g>
Compaq
> > > technical
support<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w>
Hewlett
> > > packard technical
support<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA>
Microsoft
> > > technical
support<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw>
> > > ------------------------------
> > > 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]
>