A small lesson in typology and reasoning:
CASEWHEN(STATUS_ID=1,1,0) : TYPE = INTEGER
SUM(X) : TYPE = INTEGER if X is INTEGER
X/Y : TYPE = INTEGER if X is INTEGER
Because division usually produces non integers the result is converted
to integer by rounding.
This is an arbitrary decision in general.
Java division always produces real numbers whereas H2 (and maybe SQL?)
produces numbers of the same type as dividend (that is just my quess
actually).
So in your situation if you cast the type of dividend to decimal or real
like this
SELECT
CAST(
SUM(
CASEWHEN(
STATUS_ID=1,1,0
)
)
AS DECIMAL)
/
COUNT(*)
FROM TICKET
You will be pleasantly surprised.
To analyze this case all I needed was 15 minutes and a little bit of
fact checking from H2 website.
You should try the same.
- Rami
On 3.10.2012 19:40, Gordam Gordam wrote:
Hi everyone.
I have this SQL:
SELECT SUM(CASEWHEN(STATUS_ID=1,1,0))/COUNT(*) AS PERCENT_WIN
FROM TICKET;
Result of this q/uery is always zero. I don't know why, both
aggregations return value grater then zero that is for sure, but this
does not work always ZERO!? Is this some kind of issue with h2 or not
supported?
I am using Version 1.3.169 (2012-09-09) on windows 7 if that is of
some importance.
Thank you in advance for help!
bye.
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/sF2h0SshWg4J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
