How can I group by Branch, then by Client and then by Referral Type in JavaScript.
Hi,
I have an array in JavaScript. How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaScript. Please note I need to do this client side using JavaScript.
Okay, so just extend that compareInfo function so that it also sorts by LPS:
Code:
function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// all three same:
if ( i1.lps < i2.lps ) return -1;
if ( i1.lps > i2.lps ) return 1;
// all 4 same:
return 0;
}
And then it will be easy to get the count of DISTINCT values by simply noting when the various fiels in the array change.
Hi, I used your approach and now I have branch, region, referral in order but the way I am doing it, I am only getting branch count when I am counting LPS.
How can I get the result for the following SQL statement using JavaScript.
SELECT branch, region, referral, COUNT(DISTINCT LPS) FROM table GROUP BY branch, region, referral
But since you say the other code seemed to work, maybe this will work, too.
Code:
var allInfo = [
new Info("402036","402430","Psychological File Review","30" ),
new Info("402049","402805","In-Home Assessment","10.87927" ),
... etc ...
(no comma after last one)
];
function compareInfo( i1, i2 )
{
if ( i1.branch < i2.branch ) return -1;
if ( i1.branch > i2.branch ) return 1;
// branches same:
if ( i1.client < i2.client ) return -1;
if ( i1.client > i2.client ) return 1;
// branches and clients same
if ( i1.referral < i2.referral ) return -1;
if ( i1.referral > i2.referral ) return 1;
// three the same:
if ( i1.lps < i2.lps ) return -1;
if ( i1.lps > i2.lps ) return 1;
// all 4 same:
return 0;
}
var sortedInfo = allInfo.sort( compareInfo );
var info = sortedInfo[0];
priorBranch = info.branch;
priorClient = info.client;
priorReferral = info.referral;
priorLps = info.lps;
lpsCount = 0;
for ( var i = 0; i < sortedInfo.length; ++i )
{
info = sortedInfo[i];
curBranch = info.branch;
curClient = info.client;
curReferral = info.referral;
curLps = info.lps;
if ( curBranch != priorBranch
|| curClient != priorClient
|| curReferral != priorReferral
|| curLps != priorLps
) {
writeRow( priorBranch, priorClient, priorReferral, priorLps, lpsCount );
priorBranch = curBranch;
priorClient = curClient
priorReferral = curReferral;
priorLps = curLps;
lpsCount = 0;
}
// then whether changed or not, we bump the count:
++lpsCount;
}
// write last row:
writeRow( priorBranch, priorClient, priorReferral, priorLps, lpsCount );
// THIS IS JUST A SAMPLE
// of what you *MIGHT* use for writeRow:
function writeRow( br, cl, ref, lps, cnt )
{
document.write(
"<tr>" +
"<td>" + br + "</td>" +
"<td>" + cl + "</td>" +
"<td>" + ref + "</td>" +
"<td>" + lps + "</td>" +
"<td>" + cnt + "</td>" +
"</tr>" );
}
As you can see, we do *NOT* write a row until we have gotten a *CHANGE* in one or more of the values. That way, we can get an accurate count of the UNIQUE valules (that is, where all 4 fields match).
This means we have to have a "clean up" write of the last row, as shown there, so it will work better to have the writeRow( ) be a separate function.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Before you post, read our: 
