1 2 Previous Next 25 Replies Latest reply on Apr 4, 2015 11:35 AM by openspace

    SQL: SELECT SUM of column2 in relation to column1

    openspace

      I created this calculation to determine the total memberships that are active according to a user set start and end date. The calc lists all the membership renewals in a year with their corresponding expiry and creation dates. Because the start and end date are determined by the user member renewals can repeat, but I would only like to count the number of unique members. The calculation below does this part perfectly.

       

      The second number I would like to calculate, which I'm having troubles with, is the sum associated with each distinct contact to determine the total amount associated which each active membership. The sql below only works if all the active memberships between the start and end date already happen to be distinct, but results incorrectly if a membership repeats.

       

      Is there a way to determine the amount total in relation to the first column that is selected?

       

      memberships= ExecuteSQL ("

      SELECT COUNT (DISTINCT MContactIds), Sum(Amount)

      FROM \"Line Items\"

      WHERE MDateExpiry > EndDate

      AND MDateCreation  <= EndDate

      " ;¶; ¶) ;

        • 1. Re: SQL: SELECT SUM of column2 in relation to column1
          mikebeargie

          Off the top of my head, I would think it would look something more like this:

           

          ExecuteSQL ("

          SELECT COUNT (MContactIds), Sum(Amount)

          FROM \"Line Items\"

          WHERE MDateExpiry > EndDate

          AND MDateCreation  <= EndDate

          GROUP BY MContactIds

          " ;¶; ¶)

           

          your calculation will only ever return two numbers because you're not subsummarizing your data using GROUP BY.

           

          I would think that COUNT (DISTINCT MContactIds) would only return to you the number of groups you have, not the subtotal for each group.

          • 2. Re: SQL: SELECT SUM of column2 in relation to column1
            user19752

            And, you don't use StartDate then the result is "members at the EndDate", so may be unique without DISTINCT.

            Do you want "number of members" and "sum of amount" at "EndDate" ?

            • 3. Re: SQL: SELECT SUM of column2 in relation to column1
              nicolai

              Mike Beargie With GRUP BY the calculation will return multiple records, SUM for every GROUP BY  MContactIds. So you will need to expand the query and add SUM to the count results to return a single value. I have a feeling the result is not going to be right,

               

               

              openspace any chance to clarify your request, could you add a sample output you are expecting? Are you expecting a single record with two totals or multiple records with total for each member? One more thing - user19752 has a point, incuding StartDate in your calculations would help

              • 4. Re: SQL: SELECT SUM of column2 in relation to column1
                openspace

                Sure thing.

                 

                StartDate=2014/04/01 EndDate=2015/03/11

                 

                ContactIDsCreationExpiryRenewalNameTotal
                1112013-12-012014-12-012013-12-01Anne30
                1112013-12-012015-12-012014-12-01Anne20
                1122013-12-012015-12-012014-12-01Bob50

                 

                Example SQL (different from above, but simpler and illustrates the same point). This simply counts all unique lapsed memberships that did not also renew that expire after the StartDate. The sum of Total however is not searching for unique values, but rather all expired memberships after the StartDate.

                 

                Let ( [

                membersexpired= Let ( [

                  memberIDs =

                    ExecuteSQL ( "

                      SELECT (\"MContactIds\")

                      FROM \"Line Items\"

                  WHERE MDateExpiry >= StartDate

                      " ; "" ; "" )

                    ] ;

                  Case (

                    not IsEmpty ( memberIDs ) ;

                    ExecuteSQL ( "

                      SELECT COUNT (*)

                      FROM Contacts

                      WHERE \"CONTACT ID MATCH FIELD\" IN (" & Substitute ( memberIDs ; ¶ ; "," ) & ")

                       " ; "" ; ""

                    ) ;

                    0

                  )

                ) ;

                membershipsdollarsexpired = ExecuteSQL ("

                SELECT COUNT (DISTINCT MContactIds), SUM (Total)

                FROM \"Line Items\"

                WHERE MDateExpiry >= StartDate

                " ;¶; ¶) ;

                 

                membersrenewed= Let ( [

                  memberIDs =

                    ExecuteSQL ( "

                      SELECT (\"MContactIds\")

                      FROM \"Line Items\"

                      WHERE MDateRenewal >= StartDate

                      " ; "" ; "" )

                    ] ;

                  Case (

                    not IsEmpty ( memberIDs ) ;

                    ExecuteSQL ( "

                      SELECT COUNT (*)

                      FROM Contacts

                      WHERE \"CONTACT ID MATCH FIELD\" IN (" & Substitute ( memberIDs ; ¶ ; "," ) & ")

                       " ; "" ; ""

                    ) ;

                    0

                  )

                ) ;

                 

                membershipsdollarsrenewed = ExecuteSQL (Total)

                FROM \"Line Items\"

                WHERE MDateRenewal >= StartDate

                " ;¶; ¶)

                ] ;

                 

                membersexpired - membersrenewed  & ¶ & GetValue(membershipsdollarsexpired;1) - GetValue(membershipsdollarsrenewed;1 ) &¶ &  GetValue(membershipsdollarsexpired;2) - GetValue(membershipsdollarsrenewed;2) )

                 

                Incorrect Result:

                2

                2

                100

                 

                Desired Result:

                2

                2

                80

                • 5. Re: SQL: SELECT SUM of column2 in relation to column1
                  user19752

                  This example may be too simple.

                  You can use AVG(Total) or Min(Total) or Max(Total) for GROUP of ContactIDs since all Total is same in example, but?

                  • 6. Re: SQL: SELECT SUM of column2 in relation to column1
                    user19752

                    You can forget to get 1st column of COUNT (if Total doesn't have null value? not tested). Get only 1 column as

                    SELECT SUM(Total)

                    ...

                    GROUP BY ContactIDs

                     

                    and use FM function ValueCount(result) to get count of distinct ids.

                    • 7. Re: SQL: SELECT SUM of column2 in relation to column1
                      openspace

                      I've edited the example to be a little more complex

                      • 8. Re: SQL: SELECT SUM of column2 in relation to column1
                        user19752

                        Which reason do you select 30, first or max or ??

                         

                        And do you really want "after the StartDate", not "between StartDate and EndDate" ?

                        • 9. Re: SQL: SELECT SUM of column2 in relation to column1
                          openspace

                          You know what, you just made me realize that I was leaving too much information out, you're right it does not make sense as to why I want the first line of information. I've revised the example to include all my code. I hope it's not too messy, daylight savings has left me fairly exhausted.

                           

                          In the example function I would like to know the members that lapsed, but did not renew. To find this number I subtract all of the lapsed memberships from those that renewed, to find the total lapsed memberships that did not also renew. Both Anne and Bob lapsed, but Anne also renewed. The first line for Anne is the lapsed membership, while the second is the renewal. I hope that part makes sense.

                           

                          Thanks for barring with me!

                          • 10. Re: SQL: SELECT SUM of column2 in relation to column1
                            user19752

                            It may be simple, but your example makes things difficult.

                            If input data as

                            1112013-12-012014-12-012013-12-01Anne30
                            1112013-12-012015-12-012014-12-01Anne20
                            1122013-12-012015-12-012014-12-01Bob50

                            and want output

                            2

                            2

                            80

                            then 2nd record is not used at all, is this true?

                             

                            If the date span is

                            StartDate=2014/04/01 EndDate=2015/03/11

                            then Bob is not expired in the term, (2015-12-01 > 2015/3/11) is this need in count?

                            • 11. Re: SQL: SELECT SUM of column2 in relation to column1
                              openspace

                              Sorry for the late reply, I've had a busy week preparing for a new staff member.

                               

                              Using the example the output would be:

                              1

                              1

                              30

                               

                              If the input was altered to this:

                              ContactIDsCreationExpiryRenewalNameTotal
                              1112013-12-012014-12-012013-12-01Anne30
                              1112013-12-012015-12-012014-12-01Anne20
                              1122013-12-012014-12-012013-12-01Bob50

                              The output would be:

                              2

                              2

                              80

                               

                              Essentially I want the total that is associated with the distinct values of contactIDs using the calc.

                              • 12. Re: SQL: SELECT SUM of column2 in relation to column1
                                openspace

                                Essentially I'm looking for something that says

                                 

                                WHEN "field1" is distinct then "field2"

                                 

                                where field two is associated with the distinct field1

                                • 13. Re: SQL: SELECT SUM of column2 in relation to column1
                                  user19752

                                  Hmm, you want "the members that lapsed, but did not renew", then do you really need to include "Anne"?

                                  Member "Anne" "lapsed" on 1st record but "renewed" on 2nd record, so it seems "Anne" should be excluded from output, isn't it?

                                  • 14. Re: SQL: SELECT SUM of column2 in relation to column1
                                    openspace

                                    hmm, yes you're right so then the desired output would be

                                    1

                                    1

                                    50

                                     

                                    apologies, i'm confusing myself because of the final calculation in the let statement.

                                    1 2 Previous Next