12 Replies Latest reply on Mar 2, 2015 2:30 PM by openspace

    SQL: How to ORDER BY calc: number type

    openspace

      Good afternoon!

       

      I have an SQL calculation that I am trying to deploy within the y-axis of a bar chart and I am encountering the not so desired result of a "?." If someone could help me troubleshoot this I would be jumping out of my seat with glee.

       

      Here is the troublesome function:

      ExecuteSQL(

      "SELECT SUM(Total)

      FROM Donations

      WHERE DateCreation >= StartDate AND DateCreation <= EndDate

      GROUP BY Month

      ORDER BY Month DESC";

      ""; ¶)

       

      Here are the fields:

      DateCreation: unstored calculation, date type

      StartDate: unstored calculation, date type

      EndDate: unstored calculation, date type

      Month: unstored, calculation, number type: Month(DateCreation)

       

      After some quick experimentation I realized that if I change the Month field to a text type field and rejig the calculation from Month(DateCreation) to MonthName(DateCreation), the sql function results with the desired solution but it orders the months alphabetically rather than by the number of the month. Is there a way to order the sql function by a calculation field that is a number type rather than a text? Essentially I want the months to show in the order (1, 2, 3, 4, 5, 6, 7, 8 9, 10, 11, 12) on my chart. I am not familiar with SQL, so I apologize if my question is fairly simple.

        • 1. Re: SQL: How to ORDER BY calc: number type
          openspace

          With a little bit of messy get arounds I think I've figured out a solution. Feel free to one up me!

           

          I changed the field "Month" to a text type calculation and using a case function I changed the months from numbers 1–2 to letters a–l. Then inside of my chart I changed my axis calculation to the following lengthy but effective lines. FYI I changed my field name from Month to MonthReorder. This essentially checks each month to see what the sum of all the donations are for each month within a given fiscal year.

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'a'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'b'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'c'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'd'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'e'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'f'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'g'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'h'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'i'";

          ""; "")

           

          & ¶ &


          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'j'";

          ""; "")

           

          & ¶ &


          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'k'";

          ""; "")

           

          & ¶ &

           

          ExecuteSQL(

          "SELECT Sum(Total)

          FROM Donations

          WHERE DateCreation >= StartDate AND DateCreation <= EndDate

          AND MonthReorder = 'l'";

          ""; "")

          • 2. Re: SQL: How to ORDER BY calc: number type
            openspace

            I realize now, that when I create a second series using this code so the user can view the Current and Previous years at the same time. The code no longer works. A false positive as they say.

            • 3. Re: SQL: How to ORDER BY calc: number type
              matthew_odell

              Hey there,

               

              The problem with your first statement is about something unique in FileMaker. When you're using Group By, you can only Order by or Group by fields that are part of the Select statement. If you change your statement to something like this, it should work:

               

              ExecuteSQL(

              "SELECT SUM(Total), Month

              FROM Donations

              WHERE DateCreation >= StartDate AND DateCreation <= EndDate

              GROUP BY Month

              ORDER BY Month DESC";

              ""; ¶)

               

              Then the problem is that you have to pull Months out of the result. I have a recursive custom function that allows you to pull a specific column, so let me know if you'd like that.

              • 4. Re: SQL: How to ORDER BY calc: number type
                siplus

                The way I would solve this is the following.

                 

                See attachment.

                • 5. Re: SQL: How to ORDER BY calc: number type
                  openspace

                  Thanks Mathew, this helps immensely.

                   

                  If you could pass the custom function along as well. I could actually use it for another issue I'm trying to solve.

                  • 6. Re: SQL: How to ORDER BY calc: number type
                    openspace

                    The reason I'm using SQL is because I'm pulling data from unrelated tables, but regardless thank you.

                    • 7. Re: SQL: How to ORDER BY calc: number type
                      user19752

                      Your query needs to return only SUM, so order it using number of month (1st you did)

                      MonthNum: unstored, calculation, number type: Month(DateCreation)

                      don't use "Month" as field name, it is keyword in SQL.

                       

                      When you return month names ordering month number, use both field in GROUP like

                      SELECT MonthName

                      ...

                      GROUP BY MonthNum,MonthName

                      ORDER BY MonthNum DESC

                      • 8. Re: SQL: How to ORDER BY calc: number type
                        mardikennedy

                        I think this is a rather nice demo (esp in a relationship-free zone) - thank you!  It'd be even nicer if it was entirely open :-)  One thing:  I changed the dates to Dec 1 - 31 (no reason, just experimenting) and the bar label defaulted to January.  A little tweak maybe?

                         

                        Cheers, Mardi

                        • 9. Re: SQL: How to ORDER BY calc: number type
                          siplus

                          I am using SQL too, but it went unnoticed I presume.

                          • 10. Re: SQL: How to ORDER BY calc: number type
                            siplus

                            Here comes the tweak 

                            • 11. Re: SQL: How to ORDER BY calc: number type
                              mardikennedy

                              Thank you!  I'll be able to take a look later in the day.   (And without being too much of a suck, when I watched the scripts via the debugger, I thought that the sql was very elegant.)

                              • 12. Re: SQL: How to ORDER BY calc: number type
                                openspace

                                Sorry about that! I'll have to study the demo sometime this week. It's definitely a lot cleaner than my solution. It would take me a bit longer to adapt as my chart automatically orders itself starting with the fiscal year month, but after a bit of studying I'm sure I could apply this.