MySQL Forums
Forum List  »  Newbie

Adding values returned by GREATEST
Posted by: Sukhjinder Narula
Date: May 03, 2016 08:08AM

Hello,

i have a question regarding the GREATEST function of mysql.

I would like to add the values returned by GREATEST function is mysql, so a query is like below:

For example table t has 6 fields with values as follows: A = 1, B = 3, C=0, D = 0, E = 1 and F = 0 and I run a query:

SELECT
GREATEST (A, B, C) AS G1,
GREATEST (D, E, F) AS G2,
(
GREATEST (A, B, C) + GREATEST(D, E, F)

) AS Total
FROM t

The result row I expect is: 3, 1, 4
But I get 3, 1, 6

However when I run the query like below I get correct results as total being 4:

SELECT
(
GREATEST (1, 3, 0) + GREATEST(0,1,0)

) AS Total

So what I noticed is as I add result from GREATEST function, the result is adding 1 for each GREATEST call I have in total. So, if I change my query as below:

SELECT
GREATEST (A, B, C) AS G1,
GREATEST (D, E, F) AS G2,
(
GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F)

) AS Total
FROM t

The results will be 3, 1, 8

GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F) is calculated as

GREATEST (A, B, C) = 3 + 1
GREATEST(D, E, F) = 1 +1
GREATEST(D, E, F) = 1 +1

So the total is 8.

I have tried online to search for this type of behaviour but no luck. Can anyone please explain this.

Many Thanks,
SK

Options: ReplyQuote


Subject
Written By
Posted
Adding values returned by GREATEST
May 03, 2016 08:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.