Hi,

select
mnr,
kw,
sum(pzt),
sum(uu),
sum(u01),
sum(u02),
sum(u03)
from mde
where mnr=63210 group by kw, mnr


results in:

mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
63210 02 7738 50 131 0 1086
63210 03 7584 133 143 42 1162
63210 04 7460 248 166 0 976
63210 05 8921 159 76 0 689
63210 06 8262 226 145 0 784
63210 07 7708 146 91 22 1337

In every selection I need the column sum(pzt) and the 2 peak-values
of sum(u1), sum(u2) and sum(u3). The other value should be replaced
by zero.

e.g. for the first line (summ(uu) changed to 0)
mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
63210 02 7738 0 131 0 1086

e.g. for the second line (no changes)
mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
63210 02 7738 0 131 0 1086

e.g. for the third line (sum(uu) and sum(u02) changed to 0)
mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
63210 03 7584 0 143 0 1162

I tried a combination of select .. greatest didn't deliver the
result I expected..

Can anyone point me to a solution?

regards
Volker

On Dec 11, 2007 6:30 AM, Volker Duetsch <volker.duetsch@gmx.net> wrote:
> Hi,
>
> select
> mnr,
> kw,
> sum(pzt),
> sum(uu),
> sum(u01),
> sum(u02),
> sum(u03)
> from mde
> where mnr=63210 group by kw, mnr
>
>
> results in:
>
> mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
> 63210 02 7738 50 131 0 1086
> 63210 03 7584 133 143 42 1162
> 63210 04 7460 248 166 0 976
> 63210 05 8921 159 76 0 689
> 63210 06 8262 226 145 0 784
> 63210 07 7708 146 91 22 1337
>
> In every selection I need the column sum(pzt) and the 2 peak-values
> of sum(u1), sum(u2) and sum(u3). The other value should be replaced
> by zero.
>
> e.g. for the first line (summ(uu) changed to 0)
> mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
> 63210 02 7738 0 131 0 1086
>
> e.g. for the second line (no changes)
> mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
> 63210 02 7738 0 131 0 1086
>
> e.g. for the third line (sum(uu) and sum(u02) changed to 0)
> mnr kw sum(pzt) sum(uu) sum(u01) sum(u02) sum(u03)
> 63210 03 7584 0 143 0 1162
>
> I tried a combination of select .. greatest didn't deliver the
> result I expected..
>
> Can anyone point me to a solution?
>
> regards
> Volker

You refference columns in the statement "I need the column sum(pzt)
and the 2 peak-values of sum(u1), sum(u2) and sum(u3)." which do not
seem to exist in your result set. Also it looks like you are
refferencing (based on 'sum(pzt)' being 7738 ) the first line in
your example "for the second line". I could be smoking crack again
though....

Would it be correct to restate what you wanted as:
"I need the column sum(pzt) and the 2 peak-values of sum(uu),
sum(u01), sum(u02) and sum(u03)."

I would probably build out a solution based on if statements. Something like:

select
mnr,
kw,
sum(pzt),
IF( (sum(uu) > sum(u01) AND sum(uu) > sum(u02)) OR
(sum(uu) > sum(u02) AND sum(uu) > sum(u03)) OR
(sum(uu) > sum(u01) AND sum(uu) > sum(u03)),
sum(uu), 0) as sum(uu),
.....

I bet there is a cleaner solution.