6 Replies Latest reply on Oct 16, 2015 5:18 AM by user19752

    Sum of day of week

    KenDeFilipps

      If I have a field called DayOfWeek which is a text field containing various days of the week, how can I get a sum total or count of each day of the week.  I did create 7 new fields called sum_Sunday, sum_monday, etc. and tried various calculations, but having no luck. I have  a feeling I'm over thinking this and making it harder than it has to be.  Any suggestions would be greatly appreciated.

      Thanks.

        • 1. Re: Sum of day of week
          user19752

          ExecuteSQL ("

          SELECT \"DayOfWeek\", COUNT(\"DayOfWeek\")

          FROM \"tablename\"

          GROUP BY \"DayOfWeek\"

          "; "";"" )

          then you get all result in one field.

           

          You should set the field storage to "global", for not calculate it on all records.

          • 2. Re: Sum of day of week
            user19752

            DayOfWeek is a name of function, so I don't prefer to use as field name.

            • 3. Re: Sum of day of week
              KenDeFilipps

              Hi.

              Thanks.

              That worked to a degree, but I get counts for every variation of the day, ex.,

              FRIDAY,1

              Friday,355

              MONDAY,5

              Monday,320

              SUNDAY,4

              Saturday,257

              Sunday,205

              THURSDAY,3

              TUESDAY,2

              Thursday,350

              Tuesday,348

              WEDNESDAY,5

              Wednesday,365

               

              And I need these Sum's for a chart/graph, so all data in one filed probably won't work.

              • 4. Re: Sum of day of week
                user19752

                Variation is bad thing. And you would want to be sorted as Sun...Sat or Mon...Sun.

                In one field is not problem for using chart, it has "listed items" as data source option.

                 

                Add a calculation field as (if you want Sun...Sat)

                 

                Position ( "sumotuwethfrsa" ; Left ( DayOfWeek ; 2 ) ; 1 ; 1 )

                 

                Then use it in SQL

                 

                ExecuteSQL ("

                SELECT COUNT(\"addedField\")

                FROM \"tablename\"

                GROUP BY \"addedField\"

                "; "" ; "" )

                 

                This can be data source of chart. X axis is static text calculation as  "Sun¶Mon¶Tue¶Wed¶Thu¶Fri¶Sat"

                • 5. Re: Sum of day of week
                  KenDeFilipps

                  Thanks again.  I'm not sure this method is going to work.  The initial calculation is painfully slow.  Additionally, the field does not update with subsequent found sets. Given that the sum of each day of the week will be used for reporting and charting, the data will vary depending upon the search criteria.

                  • 6. Re: Sum of day of week
                    user19752

                    I wrote SQL without WHERE clause, it always calculate based on all records in the table.

                    For adding search criteria, insert something before GROUP BY

                    WHERE \"somefield\" = 'somevalue'

                     

                    And I noticed that if any of dow has no data, the SQL is not good.