Thanks i made it work, but i just get a bunch of 0.00

Here is my select that is working:

SELECT N.vnd_cod,
SUM((COALESCE(P.pro_com_porcento,V.ved_comissao) /100) * (I.item_qtde *
I.item_valor)) AS porcento,
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 V.ved_cod='2'
GROUP BY N.vnd_cod
ORDER BY N.vnd_cod ASC

It runs and return me the vnd_cod, but all the values are 0.00

Is there something wrong with it?

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

>
> > 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]
> >
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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]