Re: Divide two aggregated values

[Gordam Gordam]

I wrote UDF as workaround but I don't want to use it if it is not necessary. Anyone?

On Wednesday, October 3, 2012 6:40:18 PM UTC+2, 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.

[Rami Ojares]

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 h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Gordam Gordam]

Well I prefer to solve problems with communication first. I like when others work for me, so thank you Rami, good job. Next time just help someone if you want, and shoosh, jesus...