using DISCTINCT on a query entity COUNT function

Question

I created a view and need to dynamically do some aggregations. For example, I have below piece of SQL code: 
 SELECT COUNT (DISTINCT ENTITY_ID) COUNT_ROWS, A.PRODUCT FROM P360_V_RCT_COUNT A GROUP BY A.PRODUCT 
 Notice the DISTINCT on the COUNT function. How can I accomplish this in Appian? 
 
 My query entity looks like below. So all I need is to somehow add DISTINCT on this line: a!queryAggregationColumn(field: "entityId", alias: "entityCount", aggregationFunction: "COUNT"). It seems it is not possible so what are my alternatives? Or is it possible? 
 a!queryEntity(
 entity: cons!P360_RCT_VIEW_COUNT,
 query: a!query(
 aggregation: a!queryAggregation(
 aggregationColumns: {
 a!queryAggregationColumn(field: "entityId", alias: "entityCount", aggregationFunction: "COUNT"),
 if(
 contains(ri!listOfColumns, "phaseStatusDesc"), 
 a!queryAggregationColumn(field: "phaseStatusDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "participationStatusDesc"),
 a!queryAggregationColumn(field: "participationStatusDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "entityTypeDesc"),
 a!queryAggregationColumn(field: "entityTypeDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "productDesc"),
 a!queryAggregationColumn(field: "productDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "countyDesc"),
 a!queryAggregationColumn(field: "countyDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "zip"),
 a!queryAggregationColumn(field: "zip", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "accountExecutive"),
 a!queryAggregationColumn(field: "accountExecutive", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "providerServiceTypeDesc"),
 a!queryAggregationColumn(field: "providerServiceTypeDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "regionDesc"),
 a!queryAggregationColumn(field: "regionDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "territoryDesc"),
 a!queryAggregationColumn(field: "territoryDesc", isGrouping: true()), {}
 ),
 if(
 contains(ri!listOfColumns, "serviceDesc"),
 a!queryAggregationColumn(field: "serviceDesc", isGrouping: true()), {}
 )
 }
 ),
 logicalExpression: a!queryLogicalExpression(
 operator: "AND",
 filters: {
 /* DEFAULT FILTER IS NEEDED */
 a!queryFilter(field: "entityId", operator: "not null"),
 if(
 rule!APN_isBlank(ri!phaseStatuses), 
 {},
 a!queryFilter(field: "phaseStatusId", operator: "in", value: ri!phaseStatuses)
 ),
 if(
 rule!APN_isBlank(ri!participationStatuses), 
 {},
 a!queryFilter(field: "participationStatusId", operator: "in", value: ri!participationStatuses)
 ),
 if(
 rule!APN_isBlank(ri!entityTypes), 
 {},
 a!queryFilter(field: "entityTypeId", operator: "in", value: ri!entityTypes)
 ),
 if(
 rule!APN_isBlank(ri!products), 
 {},
 a!queryFilter(field: "productId", operator: "in", value: ri!products)
 ),
 if(
 rule!APN_isBlank(ri!counties), 
 {},
 a!queryFilter(field: "countyId", operator: "in", value: ri!counties)
 ),
 if(
 rule!APN_isBlank(ri!zip),
 {},
 a!queryFilter(field: "zip", operator: "in", value: tointeger(split(ri!zip, ";")))
 ),
 if(
 rule!APN_isBlank(ri!accountExecutives), 
 {},
 a!queryFilter(field: "accountExecutive", operator: "in", value: ri!accountExecutives)
 ),
 if(
 rule!APN_isBlank(ri!providerServiceTypes), 
 {},
 a!queryFilter(field: "providerServiceTypeId", operator: "in", value: ri!providerServiceTypes)
 ),
 if(
 rule!APN_isBlank(ri!regions), 
 {},
 a!queryFilter(field: "regionId", aoperator: "in", value: ri!regions)
 ),
 if(
 rule!APN_isBlank(ri!territories), 
 {},
 a!queryFilter(field: "territoryId", operator: "in", value: ri!territories)
 ),
 if(
 rule!APN_isBlank(ri!services), 
 {},
 a!queryFilter(field: "serviceId", operator: "in", value: ri!services)
 )
 }
 ),
 pagingInfo: a!pagingInfo(
 1,
 - 1
 )
 )
)

aloks0189 · Answer

erickp DISTINCT statement is used to return only distinct (different) values, we can consider without duplicate rows, so as you have mention above, you have created a view. 
 
When i talk in SQL Query side then, i can find that, you have used DISTINCT but i think it&#x27;s not in proper structure, you can try as below: 
 
SELECT DISTINCT COUNT(yourColumn) as COUNT_ROWS, A.PRODUCT as product 
FROM P360_V_RCT_COUNT A 
GROUP BY A.PRODUCT 
 
So here i kept DISTINCT just after the Select, so once this view starts to return the DISTINCT column then we can create a normal queryEntity which can do our Job. 
 
Coming to your requirement: 
I think for your requirement View is the right choice instead of doing it through QueryEntity, because i don&#x27;t think we have any DISTINCT function support in Appian. Because as per the Appian Documentation: aggregationFunction (Text): The function to use when aggregating the field. Valid values include COUNT, SUM, AVG, MIN, and MAX.