7 Replies Latest reply on Sep 29, 2014 9:08 PM by ariley

    SQL count(*) wrong result

    ariley

      I have the following SQL calculation:

       

      ExecuteSQL (

      "SELECT Count(*),

      sum ( case when Case_Type='General Liability' then 1 else 0 end ) GLCount,

      sum ( case when Case_Type='Auto' then 1 else 0 end ) AutoCount,

      sum ( case when Case_Type='Workers Compensation' then 1 else 0 end ) WCCount,

      sum ( case when Case_Type='Other' then 1 else 0 end ) OtherCount

      FROM charting_case

      GROUP BY Intake_Month_Name"

      ; "," ; ¶

      )

       

       

      Here's what the data viewer looks like with the result: http://cloud.zerobluetech.com/image/3Y1u2S33460L

       

      The charting_case TO is connected to charting, and the records are constrained to the entire year before the last of this month. This works fine and FileMaker returns the correct data set when checked.

       

      I want to see a comparison from month-to-month. So I need a number for every month for every case type. In the case table I have a calc that is the Intake_Month_Name (returns the month of the date).

       

      Now, when I constrain the records to just show the General Liability for last September, FM gives me 33 records and SQL gives me 385.

       

      So ideally I'd like to have a dataset that looks like this:

       

      x,x,x,x

      y,y,y,y

      z,z,z,z

       

      where the rows will be the months and the letter would be substituted with the proper count of case types for each. I tried grouping by the Intake_Month_Year field, as well, but then I get even more wacky results.

       

      Can anyone can help me tackle this?

       

      Thanks.

        • 1. Re: SQL count(*) wrong result
          beverly

          You possibly need a UNION.

           

          -- sent from myPhone --

          Beverly Voth

          --

          • 2. Re: SQL count(*) wrong result
            steve_ssh

            Hello Agnes,

             

            I can't honestly say that I understand 100% the scenario that you describe, but one thing that looks suspect to me is:

             

            I see no constraint in the above query which limits which records from CHARTING_CASE are to be included in the calc.

             

            I'm kind of expecting to see something like what you've got posted above, but with a WHERE clause that specifically targets the records in the date range that you want.

             

            Otherwise all records will all be included in the ExecuteSQL tally regardless of the current status of any relationship between CHARTING and CHARTING_CASE.

             

            I'm wondering if that's what is going on here.  Are there multiple years of records in the CHARTING_CASE table?

             

             

            HTH & very best,

             

            -steve

             

             

            p.s.  One semi-related tidbit that I took away from playing around with ExecuteSQL is that, whenever I'm using a GROUP BY clause, it's worth seeing if I can get the same result, but with better performance, by using a hybrid approach which uses regular old FM calcs to concatenate a series of ExecuteSQL statements.  It sounds inelegant, I know, (and it feels inelegant, too), but I've found that sometimes such an approach yielded better performance.

            • 3. Re: SQL count(*) wrong result
              ariley

              Hi Steve,

               

              Thank you.

               

              So, that may be the problem, indeed. But I did try once for fun to target the 'case' table and I remember getting the question mark.

               

              Tomorrow I'll take a look.

               

              Thank you.

               

              Best regards,

              Agnes Riley

              www.zerobluetech.com

              877 917-9079

               

              Sent from my iPhone

              • 4. Re: SQL count(*) wrong result
                ariley

                Okay, so this is not getting me the right count either. Intake_Month_Name is a calculation that gives me the month name for the Intake_Date. If I use "CASE" for the table it doesn't give me any results. And CASE is the table, charting case is the TO.

                 

                "SELECT Count(*)

                FROM charting_case

                WHERE Case_Type='Auto' and

                Intake_Date between '2013-9-1'

                AND '2014-9-30'

                GROUP BY Intake_Month_Name"

                 

                And what's the best way to concatenate results? I do get this working I'll hopefully end up with multiple variables that will look like this (summing up how many casess have the case_type "auto" for month one, two three, etc.

                 

                115

                84

                56

                78

                etc.

                 

                and I need to combine 4 of them so they end up looking like this:

                 

                115, 125, 45, 33

                84, 45, 56, 23

                etc.

                 

                Thank you.

                • 5. Re: SQL count(*) wrong result
                  steve_ssh

                  Hi Agnes,

                   

                  I would check out the syntax you are using for using dates in your query.

                   

                   

                  Rather than:

                   

                     Intake_Date BETWEEN '2013-9-1' AND '2014-9-30'

                   

                   

                   

                  I believe that you want to be using the DATE term to let ExecuteSQL know that those strings are to be treated as Dates, e.g.:

                   

                       Intake_Date BETWEEN DATE '2013-9-1' AND DATE '2014-9-30'

                   

                   

                   

                  One nice way to avoid dealing with this is to supply your dates as parameters in your call to ExecuteSQL, e.g.:

                   

                      Let([

                   

                         varQUERY = "SELECT ....... FROM ..... WHERE Intake_Date BETWEEN ? AND ?"

                      ];

                   

                          ExecuteSQL( varQuery; ""; "";  Date( 9; 1; 2013 ); Date( 9; 30; 2014 ) )

                      )

                   

                   

                  As for the concatenation:

                   

                  It's my hope that it will be possible for you to craft some SQL that will generate a single row for a single month, but which will include all of the columns you need in that one row.

                   

                  That's what I'm hoping will pan out of this.  If so, that, of course, would be a much easier task as far as the concatenation goes.

                   

                  If not, we'll figure something out.

                   

                   

                   

                  Very best,

                   

                  -steve

                  1 of 1 people found this helpful
                  • 6. Re: SQL count(*) wrong result
                    user19752

                    I think you want that already you have.

                    Adding

                    WHERE Intake_Date between '2013-9-1' AND '2014-9-30'

                    (and removing COUNT(*) )

                    to the query in first post may be the answer.

                     

                    If your table name is "CASE", it should be quoted in SQL since it is reserved word.

                     

                    ...

                    FROM \"CASE\"

                    ...

                    1 of 1 people found this helpful
                    • 7. Re: SQL count(*) wrong result
                      ariley

                      Thank you. Indeed, CASE was the name of the table. I need to learn the reserved words.

                       

                      With great help from Steve we got this working and the interactive chart looks really nice: http://cloud.zerobluetech.com/image/3a2j14291i1j

                       

                      Thank you, again!