Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
two different Sums in Select |
Wed, Sep 2 2009 7:19 AM | Permanent Link |
"Burkhard Schneider" | Hi everybody,
I have this sql statement: SELECT Kostenstellen.NR, Sum(Gesamt.Kosten) as Summe FROM (Gesamt LEFT JOIN Kostenstellen ON Gesamt.NR = Kostenstellen.NR) LEFT JOIN Projekte ON Gesamt.PROJ_NR = Projekte.PROJ_NR Where Projekte.Sonder=0 Group By Kostenstellen.NR Now I want a second sum over all Projects with Projekte.Sonder>0. Both Sums should appear in one row of the result. I want something like this: SELECT Kostenstellen.NR, Sum(Gesamt.Kosten) as Summe1 (Where Projekte.Sonder=0) Sum(Gesamt.Kosten) as Summe2 (Where Projekte.Sonder>0) FROM (Gesamt LEFT JOIN Kostenstellen ON Gesamt.NR = Kostenstellen.NR) LEFT JOIN Projekte ON Gesamt.PROJ_NR = Projekte.PROJ_NR Group By Kostenstellen.NR Is this possible? |
Wed, Sep 2 2009 7:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Burkhard
Not in a single sql statement. You'll have to use a script - something along the lines of SELECT Kostenstellen.NR, Sum(Gesamt.Kosten) as Summe1 INTO Memory\Temp1 FROM (Gesamt LEFT JOIN Kostenstellen ON Gesamt.NR = Kostenstellen.NR) LEFT JOIN Projekte ON Gesamt.PROJ_NR = Projekte.PROJ_NR Where Projekte.Sonder=0 Group By Kostenstellen.NR; SELECT Kostenstellen.NR, Sum(Gesamt.Kosten) as Summe2 0 AS Summe2 INTO Memory\Temp2 FROM (Gesamt LEFT JOIN Kostenstellen ON Gesamt.NR = Kostenstellen.NR) LEFT JOIN Projekte ON Gesamt.PROJ_NR = Projekte.PROJ_NR Where Projekte.Sonder>0 Group By Kostenstellen.NR; SELECT NR, SUmme1,Summe2 FROM Memory\Temp1 T1 JOIN Memory\Temp2 T2 ON T1.NR = T2.NR; Roy Lambert [Team Elevate] |
Wed, Sep 2 2009 10:28 AM | Permanent Link |
"John Hay" | Burkhard
> I want something like this: > > SELECT Kostenstellen.NR, > Sum(Gesamt.Kosten) as Summe1 (Where Projekte.Sonder=0) > Sum(Gesamt.Kosten) as Summe2 (Where Projekte.Sonder>0) > FROM (Gesamt LEFT JOIN Kostenstellen ON Gesamt.NR = Kostenstellen.NR) > LEFT JOIN Projekte ON Gesamt.PROJ_NR = Projekte.PROJ_NR > Group By Kostenstellen.NR > > Is this possible? How about SELECT Kostenstellen.NR, Sum(If(Projekte.Sonder=0 then Gesamt.Kosten else 0)) as Summe1, Sum(If(Projekte.Sonder>0 then Gesamt.Kosten else 0)) as Summe2 FROM (Gesamt LEFT JOIN Kostenstellen ON Gesamt.NR = Kostenstellen.NR) LEFT JOIN Projekte ON Gesamt.PROJ_NR = Projekte.PROJ_NR Group By Kostenstellen.NR John |
Thu, Sep 3 2009 3:08 AM | Permanent Link |
"Burkhard Schneider" | "John Hay" schrieb:
> How about > > SELECT Kostenstellen.NR, > Sum(If(Projekte.Sonder=0 then Gesamt.Kosten else 0)) as Summe1, > Sum(If(Projekte.Sonder>0 then Gesamt.Kosten else 0)) as Summe2 > FROM (Gesamt LEFT JOIN Kostenstellen ON Gesamt.NR = Kostenstellen.NR) > LEFT JOIN Projekte ON Gesamt.PROJ_NR = Projekte.PROJ_NR > Group By Kostenstellen.NR > Thank you very much. That's exactly what I wanted. Regards Burkhard Schneider |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |