Author |
Topic |
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-15 : 12:38:51
|
Hello Everybody,Here is my problem:Table Z_GLivre:konto, Umsatz, Gegenkonto, SH1200, 25.97, 4650, H1200, 11.13, 4650, H1200, 17.52, 4824, NULL1200, 51.44, 4824, NULL1755, 300.00, 4190, H1755, 6.00, 4190, H1755, 86.97, 4250, H1755, 288.30, 4190, NULL1755, 104.67, 4190, NULLI expect an output like:TheKonto TotalCredit TotalDebit1200, 37.10, 68.961755, 392.97, 392.974190, 306.00, 392.974250, 86.97, 0.004650, 37.10, 0.004824, 0.00, 68,96I tried this:-- Part ISELECT Konto as TheKonto,SUM(Umsatz) as TotalCreditFROM Z_GLivreWHERE Konto > 0 AND SH = 'H'GROUP by KontoUNIONSELECT Gegenkonto as TheKonto,SUM(Umsatz) as TotalCreditFROM Z_GLivreWHERE Gegenkonto > 0 AND SH = 'H'GROUP by Gegenkonto,-- Part IISELECT Konto as TheKonto,SUM(Umsatz) as TotalDebitFROM Z_GLivreWHERE Konto > 0 AND SH IS NULLGROUP by KontoUNIONSELECT Gegenkonto as TheKonto,SUM(Umsatz) as TotalDebitFROM Z_GLivreWHERE Gegenkonto > 0 AND SH IS NULLGROUP by GegenkontoORDER by TheKontoPart I is fine, Part II give an error (of course)Any ideas?Thanks in advance |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 12:57:13
|
Sorry your output doesnt match with posted sample data.Where did you get 1210 etc value from?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-15 : 13:13:46
|
It was an example. There is arround 50 different Konto # |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 13:18:18
|
please post sample data and then give output from it. that will help us to understand your requirement clearly.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-15 : 13:21:46
|
Done! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 13:26:18
|
[code]SELECT Konto,SUM(CASE WHEN SH = 'H' THEN Umsatz ELSE 0 END) AS TotalCredit,SUM(CASE WHEN SH IS NULL THEN Umsatz ELSE 0 END) AS TotalDebitFROM TableGROUP BY Konto[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-15 : 13:37:16
|
Why do I always want to make it so complicated when it can be simple !!!Thank you so much visakh16 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 13:45:37
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-15 : 13:54:38
|
I actualy missed results in the output, who make the all sense of my first question! Could you please have a look?Sorry about that. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 15:19:54
|
[code]SELECT Konto,SUM(CASE WHEN SH = 'H' THEN Umsatz ELSE 0 END) AS TotalCredit,SUM(CASE WHEN SH IS NULL THEN Umsatz ELSE 0 END) AS TotalDebitFROM TableGROUP BY KontoUNION ALLSELECT Gegenkonto,SUM(CASE WHEN SH = 'H' THEN Umsatz ELSE 0 END) AS TotalCredit,SUM(CASE WHEN SH IS NULL THEN Umsatz ELSE 0 END) AS TotalDebitFROM TableGROUP BY Gegenkonto[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-15 : 15:32:37
|
Thanks Visakh,I tried this already, but I have Kontos coming out in double.Ex.:1200, 367105.62, 379174.441210, 124529.78, 126030.951360, 6000.00, 6000.001361, 2951.95, 19611.481361, 1270.07, 16975.441362, 3679.19, 3679.191570, 16098.98, 2554.061588, 1128.42, 0.001590, 24549.64, 13194.281590, 12269.55, 3857.20 |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-15 : 19:33:35
|
Try this:[CODE]SELECT Konto, SUM(CASE WHEN SH = 'H' THEN Umsatz ELSE 0 END) AS TotalCredit,SUM(CASE WHEN SH IS NULL THEN Umsatz ELSE 0 END) AS TotalDebitFROM (SELECT Konto, Umsatz, SH FROM Z_GLivreUNION ALLSELECT Gegenkonto, Umsatz, SH FROM Z_GLivre) AGROUP BY konto[/CODE] |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-16 : 04:09:39
|
Thank you very much MuMu88. It works perfectly.Now, I have an other one ;-)Similar table, so we can use the same example, but I need to do the total differently.The total won't be on:Konto + Gegenkonto Where SH = 'H' As TotalCreditKonto + Gegenkonto Where SH = '' As TotalDebitBUTKonto Where SH = 'H' + Gegenkonto Where SH IS NULL As TotalCreditKonto Where SH IS NULL + Gegenkonto Where SH = 'H' As TotalDebitAny ideas ? |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-16 : 10:28:29
|
[CODE]SELECT Konto, SUM(CASE WHEN ((SH = 'H' AND K = 1) OR (SH IS NULL AND K =2)) THEN Umsatz ELSE 0 END) AS TotalCredit,SUM(CASE WHEN ((SH = 'H' AND K = 2) OR (SH IS NULL AND K =1)) THEN Umsatz ELSE 0 END) AS TotalDebitFROM (SELECT 1 as K, Konto, Umsatz, SH FROM @Z_GLivreUNION ALLSELECT 2 as K, Gegenkonto, Umsatz, SH FROM @Z_GLivre) AGROUP BY konto[/CODE] |
|
|
Lesombrero
Starting Member
43 Posts |
Posted - 2013-06-29 : 09:34:37
|
Thanks a lot MuMu88. That is exactly what I needed! And sorry for not responding earlier (I was away...). |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-29 : 11:23:58
|
Glad to help.quote: Originally posted by Lesombrero Thanks a lot MuMu88. That is exactly what I needed! And sorry for not responding earlier (I was away...).
|
|
|
|