LinqJs - Group by using distinct count

Question

I am using LinqJs and trying to create a grouped set with a particular set of data. I have written the query in MS-SQL and would like some help to convert it into LinqJs. My main problem is trying to get the distinct count. This is my SQL query with a sample table and data.

declare @table TABLE(appID int,appName varchar(50), dcID varchar(20), projectID varchar(10))

INSERT INTO @table
values ('160146','TAP','GFTS2018123','P1'),('160146','TAP','GFTS2018123','P2')

select appID, appName, dcID, count(distinct(dcID)) as DcCount, count(distinct(projectID)) as ProjectCount 
from @table 
group by appID, appName, dcID

This is my incomplete LinqJs query

var statusData = Enumerable.From(data).GroupBy(
                null,
                null,
                "{ AppID: $.AppID, AppName: $.AppName, dcID: $.dcID,DcCount: $$.Count('$.dcID'), projectCount:$$.Count('$.projectID') }",
                "$.AppID"
            ).ToArray();

The output i expect is as

appID   appName dcID        DcCount ProjectCount
160146  TAP     GFTS2018123 1       2

Nina Scholz · Answer 1 · 2018-02-06T13:18:23.037

You could chain Distinct and Count.

DcCount: $$.Distinct('$.dcID').Count('$.dcID')

var data = [
        { appID: '160146', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P1' },
        { appID: '160146', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P2' },
        { appID: '160147', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P1' },
        { appID: '160148', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P2' },
        { appID: '160148', appName: 'TAP', dcID: 'GFTS2018124', projectID: 'P3' },
        { appID: '160148', appName: 'TAP', dcID: 'GFTS2018124', projectID: 'P3' },
        { appID: '160148', appName: 'TAP', dcID: 'GFTS2018125', projectID: 'P3' },
        { appID: '160149', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P2' }
    ],
    statusData = Enumerable
        .From(data)
        .GroupBy(
            null,
            null,
            "{ AppID: $.appID, AppName: $.appName, dcID: $.dcID, DcCount: $$.Distinct('$.dcID').Count('$.dcID'), projectCount: $$.Distinct('$.projectID').Count('$.projectID') }",
            "$.appID"
        )
        .ToArray();

console.log(statusData);

.as-console-wrapper { max-height: 100% !important; top: 0; }

<script src="https://cdnjs.cloudflare.com/ajax/libs/linq.js/2.2.0.2/linq.js"></script>

I want the output as below since the dcID is duplicate [ { "AppID": "160146", "AppName": "TAP", "DcCount": 1, "projectCount": 2 } ] — Bryan Noronha, Feb 06 '18 at 12:56

score 0 · Accepted Answer · answered Feb 06 '18 at 15:49

Try this:

var data = [
            { appID: '160146', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P1' },
            { appID: '160146', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P2' },
            { appID: '160147', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P1' },
            { appID: '160148', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P2' },
            { appID: '160148', appName: 'TAP', dcID: 'GFTS2018124', projectID: 'P3' },
            { appID: '160148', appName: 'TAP', dcID: 'GFTS2018124', projectID: 'P3' },
            { appID: '160148', appName: 'TAP', dcID: 'GFTS2018125', projectID: 'P3' },
            { appID: '160149', appName: 'TAP', dcID: 'GFTS2018123', projectID: 'P2' }                
           ];

var statusData = Enumerable
            .From(data)
            .GroupBy(function(x) { 
                        return { appID: x.appID, appName: x.appName, dcID: x.dcID };
                     }, 
                     null, 
                     function(key, array){
                        key.DcCount = 1; //always equals to one since it is presented in GroupBy clause
                        key.ProjectCount = array.Count();                        
                       
                       return key;
                     },
                     function(x){ return x.appID + '-' + x.appName + '-' + x.dcID; })
            .ToArray();

console.log(statusData);

<script src="https://cdnjs.cloudflare.com/ajax/libs/linq.js/2.2.0.2/linq.js"></script>

LinqJs - Group by using distinct count

2 Answers2