In Phoenix,When aggregating the double datatype, result produce multiple results .

[#1]

Select date, sum(double_datatype) from table where date = '2016-12-31'

Select date, sum(double_datatype) from table where date in ( '2016-12-31', '2016-12-09')

Select date, sum(double_datatype) from table where date in ( '2016-12-31', '2016-12-09', '2016-12-10')

| 2016-12-31 00:00:00.000 | 8.317797381859222E10 |

| 2016-12-31 00:00:00.000 | 8.317797381859225E10 |

| 2016-12-31 00:00:00.000 | 8.317797381859224E10 |

[#2]

how come your query is running without GROUP BY clause when you have aggregations and projection? can you please share the exact query and DDL to debug better.

[#3]

@Ankit Singhal I missed group by in the question.

DDL:

Create table CIBC_COPY.ASSETS_UNDER_MGMT ( ACCOUNT_ID VARCHAR(50) NOT NULL, CREATE_DT DATE NOT NULL, UPDATE_DT DATE NOT NULL,effective_date Date, market_value_income_standardized double,CONSTRAINT PK_ACCOUNT PRIMARY KEY (ACCOUNT_ID, CREATE_DT, UPDATE_DT, CHANGE_TYPE));

Query:

0: jdbc:phoenix:> SELECT effective_date,sum(a.MARKET_VALUE_INCOME_STANDARDIZED) AS AUM

. . . . . . . . > FROM CIBC_COPY.ASSETS_UNDER_MGMT a

. . . . . . . . > WHERE a.EFFECTIVE_DATE in (TO_DATE('20171004','yyyyMMdd')) group by effective_date;

0: jdbc:phoenix:> SELECT effective_date, sum(a.MARKET_VALUE_INCOME_STANDARDIZED) AS AUM

. . . . . . . . > FROM CIBC_COPY.ASSETS_UNDER_MGMT a

. . . . . . . . > WHERE a.EFFECTIVE_DATE in (TO_DATE('20171004','yyyyMMdd'),To_DATE('20171005','yyyyMMdd')) group by effective_date;

0: jdbc:phoenix:> SELECT effective_date, sum(a.MARKET_VALUE_INCOME_STANDARDIZED) AS AUM

. . . . . . . . > FROM CIBC_COPY.ASSETS_UNDER_MGMT a

. . . . . . . . > WHERE a.EFFECTIVE_DATE in (TO_DATE('20171004','yyyyMMdd'),To_DATE('20171005','yyyyMMdd'),TO_DATE('20171006','yyyyMMdd')) group by effective_date;