HARD? SQL with min(abs(sum of value),abs(sum of value2)) as fieldname?

[mygmat123]

I have a table

MYTABLE

Field1 field2 field3
abc 2 200
abc 2 100
abc 2 -180
bnm 3 -500
bnm 3 450
bnm 3 30
zxc 2 250
zxc 2 -200
zxc 2 -20

Whatever value is in field 1 (abc) will always have a corrsponding field2 (2)
example:
"abc" will always have "2" in field2
"bnm" will always have "3" in field2
"zxc" will always have "2" in field2
etc.etc.

I'm trying to build a sum query where you group by field1 and field2 and then....add a conditional sum field...

Example:

I need one field that would take the minimum absolute value between the sum of all records where field3>0 and field3<0

So my query should take mytable and display...

myquery
field1 field2 field3
abc 2 180 = min( abs(100+200) , abs(-180) )
bnm 3 480 = min( abs(450+30) , abs(-500) )
zxc 2 220 = min( abs(250) , abs(-200-20) )

What would the SQL be?

Thank you in advance

 

[Crowley16]

you would need an embedded query...

something like:

SELECT fld1, fld2, (
SELECT min(abs(sum(fld3))) FROM tbl WHERE fld3 <> 0
)
FROM tbl
...

 

[mygmat123]

I realize I need some sort of sub-query, but what?

The example you show I don't think accurately reflects the correct SQL, but I thank you for your help.

Does anybody REALLY UNDERSTAND SQL enough to solve this? If so please HELP!

 

[PHV]

Create a saved union query, say qrySumF3, like this:
SELECT field1, field2, Sum(field3) As SumF3
FROM myTable
WHERE field3>0
GROUP BY field1, field2
UNION
SELECT field1, field2, -Sum(field3)
FROM myTable
WHERE field3<0
GROUP BY field1, field2;
Now, what you want should be something like this:
SELECT A.field1, A.field2, Min(SumF3) As MinF3
FROM myTable A INNER JOIN qrySumF3 B
ON (A.field1=B.field1) AND (A.field2=B.field2)
GROUP BY A.field1, A.field2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mygmat123]

Thank you this is very helful!

How would I make 1 big SQL statement instead of two. I'm going to be running this through code.

THanks again!

 

[mygmat123]

I tried this, but get a syntax error, what am I doing wrong?

SELECT *, Min(Field4) as Cross
FROM (SELECT table1.field1, table1.Field2, table1.Field3, Sum(Abs(table1.Field4)) AS Field4
FROM table1
WHERE Field4>0
GROUP BY table1.field1, table1.Field2, table1.Field3;
UNION SELECT table1.field1, table1.Field2, table1.Field3, Sum(Abs(table1.Field4)) AS Field4
FROM table1
WHERE Field4<0
GROUP BY table1.field1, table1.Field2, table1.Field3)
GROUP BY table1.field1, table1.Field2, table1.Field3;


I thought I could use a SQL statement in the from Clause?

 

[mygmat123]

I SOLVED IT WITH YOUR GREAT HELP!!!!
THANK YOU