I have the following sql statement
SELECT ST.L, ST.N, Sum([C]*[U]) AS TOT, ST.A
FROM ST
GROUP BY ST.L, ST.N, ST.A, ST.U, Len([Z])
HAVING (((ST.U)>0) AND ((Len([Z]))=8));
the output is :
L N TOT A
6001821 81016 20 2347414
6001821 81149 224 2384685
6001821 81149 52 2436894
6001821 81149 130 2566079
6001821 81149 84 2596974
6001821 81202 107 2433693
6001821 81202 35 2555029
6001821 81202 974 2604224
I need to have:
L MAXTOT Count N
6001821 81202 81149
where maxtot is the N which has the max TOT value (974 in this case)
and Cont n in the N which has the greater number of recorrencies ( 4 times in this case)
Any idea?