List: General Discussion « Previous MessageNext Message » From: Rob Wultsch Date: December 11 2007 4:35pm Subject: Re: Selection of 2 peak-values and a fixed column View as plain text
```On Dec 11, 2007 6:30 AM, Volker Duetsch <volker.duetsch@stripped> 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.
```