On Dec 11, 2007 6:30 AM, Volker Duetsch <[EMAIL PROTECTED]> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
