Is it because you have the wrong table name Detaim in the from clause.

Try removing the join at the end. I assume that Detaim is meant to be detail
anyway.

Do you need the join between Master and Detail anyway? I assume that every
master

record would have related detail records and that



From Master M



would be sufficient.

>Hi,

>I have a master/detail setup for invoices! It is a classic invoice
>strusture where master table represent a header of invoice, and a
>detail table represent items of master table!
>So here is a sample of master:

id no date customer
1 10 13.03.06 customerxzy
2 15 14.03.06 customerabc
3 20 15.03.06 customerxxx

>and detail sample:

id mid article qty price type
1 1 art123 1 100 X
2 1 art554 1 150 M
3 2 art123 2 100 X
4 3 art556 3 200 X
5 3 artfff 1 150 S

>And here is a final structure that I must get with SQL statement:

id no date customer col_a col_b col_c
1 10 13.03.06 customerxyz 100 150 0
2 15 14.03.06 customerabc 200 0 0
3 20 15.03.06 customerxxx 600 0 150

>Like you can see, this resultset is contain id, no, date, customer
>fields form master table and col_a -> sum(qty*price) where type = X
>and col_b -> sum(qty*price) where type = M and col_c -> sum(qty*price)
>where type = S!

>I try with:
>SELECT M.Id, M.No, M.Date, M.Customer,
> (SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.Type
>= 'X') AS Col_a,
> (SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.Type
>= 'M') AS Col_b,
> (SELECT SUM(D.Qty*D.Price) FROM Detail D WHERE D.Mid = M.Id AND D.Type
>= 'S') AS Col_c
>FROM Master M, Detaim D
>WHERE D.Mid = F.Id

>BUT it is a wrong SQL statement, where im my error!

>Thanks for help!

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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
<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+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support

Computer
<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=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical
support

Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support


Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&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> technical support

Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support

Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support



_____

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
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



_____



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