group by with negative snd positive amont

[sal21]

I have 2 field:
Cod and Aumount
I can have:
Aaa 100, 00
Aaa -56,00
Aaa 88,00

I neef to group by Cod and retrivd two separate summs of Aumont similar:
Cod plus minus
Aaa 188, 00 -56, 00
How to in sql?

Note:
Aumont is Double Cod is Text

[HansV]

SELECT Cod, Sum(IIf([Amount]>0,[Amount],0)) AS Plus, Sum(IIf([Amount]<0,[Amount],0)) AS Minus
FROM MyTable
GROUP BY Cod

[sal21]

SELECT Cod, Sum(IIf([Amount]>0,[Amount],0)) AS Plus, Sum(IIf([Amount]<0,[Amount],0)) AS Minus
FROM MyTable
GROUP BY Cod

As usual tkx.
Sorry but i need a param from the two result:
... whre the summs minus is >= 5000, 00 and summs of plus >= 5000,00
Tkx

[HansV]

I don't know if this will work, but try

SELECT Cod, Sum(IIf([Amount]>0,[Amount],0)) AS Plus, Sum(IIf([Amount]<0,[Amount],0)) AS Minus
FROM MyTable
GROUP BY Cod
HAVING Sum(IIf([Amount]>0,[Amount],0))>=5000 AND Sum(IIf([Amount]<0,[Amount],0))<=-5000

[sal21]

I don't know if this will work, but try

SELECT Cod, Sum(IIf([Amount]>0,[Amount],0)) AS Plus, Sum(IIf([Amount]<0,[Amount],0)) AS Minus
FROM MyTable
GROUP BY Cod
HAVING Sum(IIf([Amount]>0,[Amount],0))>=5000 AND Sum(IIf([Amount]<0,[Amount],0))<=-5000

Hans the query work perfect...

but the query return the two result if condition is respected from the two param. >=5000 and <=-5000.

in my case i have:

10.020,00
1.400,47

i need:
11.420,47 -00,00

[HansV]

Does this do what you want?

Code: Select all

SELECT A.Cod, Val(Nz(B.Plus,0)) AS Plus, Val(Nz(C.Minus,0)) AS Minus
FROM 
    ((SELECT DISTINCT Cod FROM MyTable) AS A 
    LEFT JOIN 
    (SELECT Cod, Sum(IIf([Amount]>0,[Amount],0)) AS Plus
        FROM MyTable
        GROUP BY Cod
        HAVING Sum(IIf([Amount]>0,[Amount],0))>=5000) AS B
    ON A.Cod = B.Cod)
    LEFT JOIN
    (SELECT Cod, Sum(IIf([Amount]<0,[Amount],0)) AS Minus
        FROM MyTable
        GROUP BY Cod
        HAVING Sum(IIf([Amount]<0,[Amount],0))<=-5000) AS C
    ON A.Cod = C.Cod

[sal21]

Does this do what you want?

Code: Select all

SELECT A.Cod, Val(Nz(B.Plus,0)) AS Plus, Val(Nz(C.Minus,0)) AS Minus
FROM 
    ((SELECT DISTINCT Cod FROM MyTable) AS A 
    LEFT JOIN 
    (SELECT Cod, Sum(IIf([Amount]>0,[Amount],0)) AS Plus
        FROM MyTable
        GROUP BY Cod
        HAVING Sum(IIf([Amount]>0,[Amount],0))>=5000) AS B
    ON A.Cod = B.Cod)
    LEFT JOIN
    (SELECT Cod, Sum(IIf([Amount]<0,[Amount],0)) AS Minus
        FROM MyTable
        GROUP BY Cod
        HAVING Sum(IIf([Amount]<0,[Amount],0))<=-5000) AS C
    ON A.Cod = C.Cod

i cannot test now...

if i would to trnsform the number -125.004,45 to 125.004,45, i need to use a simple:

val(mytable.amont)

or?

[HansV]

You can use

Code: Select all

SELECT A.Cod, Val(Nz(B.Plus,0)) AS Plus, Val(Nz(C.Minus,0)) AS Minus
FROM 
    ((SELECT DISTINCT Cod FROM MyTable) AS A 
    LEFT JOIN 
    (SELECT Cod, Sum(IIf([Amount]>0,[Amount],0)) AS Plus
        FROM MyTable
        GROUP BY Cod
        HAVING Sum(IIf([Amount]>0,[Amount],0))>=5000) AS B
    ON A.Cod = B.Cod)
    LEFT JOIN
    (SELECT Cod, -Sum(IIf([Amount]<0,[Amount],0)) AS Minus
        FROM MyTable
        GROUP BY Cod
        HAVING -Sum(IIf([Amount]<0,[Amount],0))>=5000) AS C
    ON A.Cod = C.Cod