Search code examples
mysqlsql-serverparallel-data-warehouse

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

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!

Solution

  • 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