Distinct count of COLUMN1 for each value in COLUMN2

ShashankHNatesh

A Score Level 1

over 5 years ago

Hi Guys,

I am trying to convert the below query to a!queryEntity()

SQL: SELECT COUNT(DISTINCT `COLUMN1`),`COLUMN2` FROM `TABEL ` WHERE ` COLUMN2` IN (id1,id2) GROUP BY ` COLUMN2 `

I want distinct count of COLUMN1 for each value in from COLUMN2

I have written the below logic in appian

aggregation: a!queryAggregation(

{

a!queryAggregationColumn(

field: " COLUMN1",

aggregationFunction: "COUNT",

isGrouping :true()

) ,

a!queryAggregationColumn(

field: " COLUMN2",

isGrouping :true()

)

}

)

It is working fine for one ID, But not getting desired output when i pass multiple ID

Can someone please help, Thank You:)

Regards,

Shashank

Discussion posts and replies are publicly visible

Parents

0 Aditya GIll over 5 years ago

Please replace by the below code,

local!data: a!queryEntity(
entity: consToDatabaseEntity,
query: a!query(

aggregation: a!queryAggregation(
{
a!queryAggregationColumn(
field: "manufacturerId",
isGrouping: true
),
a!queryAggregationColumn(
field: "deviceId",
isGrouping: true,
aggregationFunction:"COUNT"
)
}
),
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: if(
rule!APN_isEmpty(
ri!manufacturerId
),
{},
{
a!queryFilter(
field: "manufacturerId",
operator: "IN",
value: ri!manufacturerId
),

}
)
),
pagingInfo: local!pagingInfo
)
)

Please let me know if its worked for you,

Thanks
Aditya
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Aditya GIll over 5 years ago

Please replace by the below code,

local!data: a!queryEntity(
entity: consToDatabaseEntity,
query: a!query(

aggregation: a!queryAggregation(
{
a!queryAggregationColumn(
field: "manufacturerId",
isGrouping: true
),
a!queryAggregationColumn(
field: "deviceId",
isGrouping: true,
aggregationFunction:"COUNT"
)
}
),
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: if(
rule!APN_isEmpty(
ri!manufacturerId
),
{},
{
a!queryFilter(
field: "manufacturerId",
operator: "IN",
value: ri!manufacturerId
),

}
)
),
pagingInfo: local!pagingInfo
)
)

Please let me know if its worked for you,

Thanks
Aditya
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

0 ShashankHNatesh
A Score Level 1
over 5 years ago in reply to Aditya GIll

I have used the same code before but i am not getting expected output.

When i pass an array of manufacturerId it will give consolidated result in the form of data subset but not the count of deviceId as expected

The output from above code will be

manufacturerId :123 deviceId: 6745
manufacturerId: 123 deviceId: 6755
manufacturerId :456 deviceId: 6756

Expected output is:

manufacturerId:123
deviceId=2 (distinct count of deviceId)

manufacturerId:456
deviceId=1 (distinct count of deviceId)

If i run the below SQL

SQL: SELECT COUNT(DISTINCT `DEVICE_ID`),`MANUFACTURERID` FROM `TABELNAME` WHERE `MANUFACTURERID` IN (123,456) GROUP BY `MANUFACTURERID`

I will get the expected output.

Thanks for your help :)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Aditya GIll over 5 years ago in reply to ShashankHNatesh

a!queryAggregationColumn(
field: "deviceId",
isGrouping: true, /* You have also passed grouping as well please make it false similar to script*/
aggregationFunction:"COUNT"
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Hema over 5 years ago in reply to ShashankHNatesh

Hi Shashank,

Please set isGrouping attribute in a!queryAggregationColumn() for the field "deviceId" to false(). You need to group by manufacturerId and get count of deviceId.

Thanks,
Hema
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel