SQL: Arithmetic operations on aggregate functions in a select statement shows wrong information

Question

I am working on a query for collecting some data on Microsoft Parallel data warehouse(PDW).

A part of the query is as follows --

Select

min(rows) as rows_min,

max(rows) as rows_max,

sum(rows) as rows_total,

cast((((max(rows)-min(rows))/sum(rows))*100) as float) as SkewPct;

from .....;

Apparently query doesn't show any error. It runs successfully but with correct data in all columns except SkewPct which shows only zeros.

Please help me out to solve this issue!

Answer 1

I think you are missing multiplication of 1.0 during divide as below:

cast((((max(rows)-min(rows))/(sum(rows)*1.0))*100) as float) as SkewPct;

Divide removes the fraction before converting it into float

Answer 2

As per MySQL documentation, FLOAT is not a valid type to use with CAST, you should use DECIMAL instead, with Maximum digits (M) and Decimal (D) as tw arguments:

Produces a DECIMAL value. If the optional M and D values are given, they specify the maximum number of digits (the precision) and the number of digits following the decimal point (the scale).

By default, value will be rounded to int with 0 decimals. Have a look at the query below:

SELECT CAST((((127-23)/541)*100) as DECIMAL), CAST(19.2237 AS DECIMAL(7, 5))

First column results in 19 whereas second column results in 19.2237.

Here's the SQL Fiddle.