Board index » delphi » SQL Statement Joins Question

SQL Statement Joins Question

Hi!

I have a problem and i cant find a solution.

This is the statment which works:

select i.nr, a.bezeichnung, sum(u.anzahl) from artint i left outer join
umsart u on i.nr=u.nr left outer join artikel a on i.nr=a.nr where
i.lager=true and a.kz<>"X" group by i.nr, a.bezeichnung

4300 rows

and now i want only the sum(u.anzahl) field where datum between "01.01.1998"
and "09.09.1998" but
i cant find the rigth position to set it. I try it on the place before
"group by", but then the statement return
not all 4300 rows.

Can you help me please!

mfG
Wolfgang Koranda
Tel.: +43 1 40104 1057
Fax: +43 1 40104 9035
BBS:+43 1 40104 1058
e-mail: wkora...@csi.com

 

Re:SQL Statement Joins Question


Quote
>select i.nr, a.bezeichnung, sum(u.anzahl) from artint i left outer join
>umsart u on i.nr=u.nr left outer join artikel a on i.nr=a.nr where
>i.lager=true and a.kz<>"X" group by i.nr, a.bezeichnung

>4300 rows

>and now i want only the sum(u.anzahl) field where datum between
"01.01.1998"
>and "09.09.1998" but
>i cant find the rigth position to set it. I try it on the place before
>"group by", but then the statement return
>not all 4300 rows.

What about "HAVING" clause after the "group by"? Use :

select i.nr, a.bezeichnung, sum(u.anzahl) from artint i left outer join
umsart u on i.nr=u.nr left outer join artikel a on i.nr=a.nr where
i.lager=true and a.kz<>"X" group by i.nr, a.bezeichnung
HAVING (datum  > "01.01.1998" and datum < "09.09.1998" )

You may need to convert the date values to appropriate format.

-- reddy

Re:SQL Statement Joins Question


Venkateswara Reddy Palle schrieb in Nachricht
<70oggo$...@bgtnsc03.worldnet.att.net>...
Quote
>>select i.nr, a.bezeichnung, sum(u.anzahl) from artint i left outer join
>>umsart u on i.nr=u.nr left outer join artikel a on i.nr=a.nr where
>>i.lager=true and a.kz<>"X" group by i.nr, a.bezeichnung

>>4300 rows

>>and now i want only the sum(u.anzahl) field where datum between
>"01.01.1998"
>>and "09.09.1998" but
>>i cant find the rigth position to set it. I try it on the place before
>>"group by", but then the statement return
>>not all 4300 rows.

>What about "HAVING" clause after the "group by"? Use :

>select i.nr, a.bezeichnung, sum(u.anzahl) from artint i left outer join
>umsart u on i.nr=u.nr left outer join artikel a on i.nr=a.nr where
>i.lager=true and a.kz<>"X" group by i.nr, a.bezeichnung
>HAVING (datum  > "01.01.1998" and datum < "09.09.1998" )

>You may need to convert the date values to appropriate format.

Thank you but i need the date field is in umsart and i also want to get 4300
rows and the
sum(u.anzahl) only where the u.date between X and Y.

Can you please help me!  ( Its urgent)

mfG
Wolfgang Koranda
Tel.: +43 1 40104 1057
Fax: +43 1 40104 9035
BBS:+43 1 40104 1058
e-mail: wkora...@csi.com

Other Threads