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

0 Vinay Kumar Rai over 5 years ago

Hi,

Please remove grouping in column1

a!queryAggregationColumn(
field: " COLUMN1",
aggregationFunction: "COUNT"

) ,

Thanks
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ShashankHNatesh
A Score Level 1
over 5 years ago in reply to Vinay Kumar Rai

I need distinct count of rows for Column1 ,if i remove isGrouping :true() for Column1, How will i get distinct row from Column1 ?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Aditya GIll over 5 years ago

Hi Shashank,

Can you please provide the complete Query code as you have mentioned "It is working fine for one ID, But not getting desired output when i pass multiple ID". And code is only displaying the select command but where condition i.e queryFilter is missing.

Thanks
Aditya
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

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

Full 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
)
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
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
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