Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Feb 2009
    Posts
    121
    Thanks
    7
    Thanked 0 Times in 0 Posts

    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.

    Array Data:

    Branch Client Referral Type LPS #
    402036 402430 Psychological File Review 30
    402049 402805 In-Home Assessment 10.87927
    402050 402993 Chiropractic Assessment 100
    402049 402805 Chiropractic File Review 10.88054
    402049 402806 Chiropractic File Review 10.88055
    402049 402806 Defense Medical 20
    402049 402807 Chiropractic Assessment 10
    402049 402807 In-Home Assessment + Form 1 10.88054
    402049 402807 Physiotherapy Assessment 10
    402049 402808 Attendant Care Assessment 10
    402049 402808 Chiropractic Assessment 10.88041
    402049 402808 In-Home Assessment 10
    402049 402816 Chiropractic Assessment 10.85316
    402049 402827 Neurological File Review 10.88047
    402049 402827 Social Work File Review 10.88044
    402050 402605 Psychological File Review 880434
    402050 402661 Physician Assessment 878203
    402050 402993 Physician Assessment 1000
    402036 402575 Psychological File Review 50

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Start by putting the data into an array of objects:
    Code:
    <script>
    function Info( b, c, r, lp )
    {
        this.branch = b;
        this.client = c;
        this.referral = r;
        this.lps = lp;
    }
    
    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;
        // all three same:
        return 0;
    }
    
    var sortedInfo = allInfo.sort( compareInfo );
    
    for ( var i = 0; i < sortedInfo.length; ++i )
    {
        var info = sortedInfo[i];
        document.write( ... format info as you want ... );
    }
    ...
    For COUNT DISTINCT LPS #, do you mean distinct no matter what branch/client/referral? Or do you mean grouped by one or more of those?
    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.

  • #3
    Regular Coder
    Join Date
    Feb 2009
    Posts
    121
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Start by putting the data into an array of objects:
    Code:
    <script>
    function Info( b, c, r, lp )
    {
        this.branch = b;
        this.client = c;
        this.referral = r;
        this.lps = lp;
    }
    
    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;
        // all three same:
        return 0;
    }
    
    var sortedInfo = allInfo.sort( compareInfo );
    
    for ( var i = 0; i < sortedInfo.length; ++i )
    {
        var info = sortedInfo[i];
        document.write( ... format info as you want ... );
    }
    ...
    For COUNT DISTINCT LPS #, do you mean distinct no matter what branch/client/referral? Or do you mean grouped by one or more of those?
    Thank you very much for the reply. I will test your code and see if it works.

    It should COUNT DISTINCT LPS # after all the group by are dome.

    Again thank you.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Let me reword my question.

    *IF* you were doing this in SQL, instead of in JS code, do you want

    Code:
    SELECT COUNT(DISTINCT LPS) FROM table;
    or do you want
    Code:
    SELECT branch, region, referral, COUNT(DISTINCT LPS) 
    FROM table
    GROUP BY branch, region, referral
    Or something else???

    Show me the SQL equivalent of what you want.
    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.

  • #5
    Regular Coder
    Join Date
    Feb 2009
    Posts
    121
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Let me reword my question.

    *IF* you were doing this in SQL, instead of in JS code, do you want

    Code:
    SELECT COUNT(DISTINCT LPS) FROM table;
    or do you want
    Code:
    SELECT branch, region, referral, COUNT(DISTINCT LPS) 
    FROM table
    GROUP BY branch, region, referral
    Or something else???

    Show me the SQL equivalent of what you want.
    I want
    SELECT branch, region, referral, COUNT(DISTINCT LPS)
    FROM table
    GROUP BY branch, region, referral


    Please note my array is dynamic.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Please note my array is dynamic.
    ??? What is that supposed to mean?

    You mean the user adds to it via code on the HTML page?
    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.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    I want
    SELECT branch, region, referral, COUNT(DISTINCT LPS)
    FROM table
    GROUP BY branch, region, referral
    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.
    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.

  • #8
    Regular Coder
    Join Date
    Feb 2009
    Posts
    121
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    Thank you very much. I tested it today and it works. Getting COUNT DISTINCT LPS # wasn't easy too but I manage. Thank you for your effort.

  • #9
    Regular Coder
    Join Date
    Feb 2009
    Posts
    121
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Okay...this is *NOT TESTED*.

    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.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    OH! Sorry!!! That will *SHOW* all of the DISTINCT LPS values, not just count them!!!

    Needs minor fix to match the SQL you showed...hang on...

    Okay, try again...I think this works:
    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      = 1;
    
    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
        ) {
            writeRow( priorBranch, priorClient, priorReferral, lpsCount );
            priorBranch   = curBranch;
            priorClient   = curClient
            priorReferral = curReferral;
            priorLps      = curLps;
            lpsCount = 1;
        } else if ( curLps != priorLps )
            ++lpsCount;  
        }
    }
    // write last row:
    writeRow( priorBranch, priorClient, priorReferral, 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>" + cnt + "</td>" +
            "</tr>" );
    }
    BUT UNTESTED!
    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.

  • Users who have thanked Old Pedant for this post:

    asifakhtar (10-05-2010)

  • #12
    Regular Coder
    Join Date
    Feb 2009
    Posts
    121
    Thanks
    7
    Thanked 0 Times in 0 Posts
    First please accept my apology for getting back to you late. Second your solution works. U Rock.
    Thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •