Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

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

New Contributor

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 REPLIES 2
Highlighted

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

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.

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

New Contributor

@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;