• State Not Answered
  • Replies 8 replies
  • Subscribers 11 subscribers
  • Views 6446 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Data and Records

Distinct count of COLUMN1 for each value in COLUMN2

ShashankHNatesh
A Score Level 1

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
    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
  • 0
    A Score Level 1
     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 :)
  • 0 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"
    )
  • 0 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
Reply Children
No Data
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.