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

# Sum of day of week

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

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

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

• ###### 3. Re: Sum of day of week

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

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 ("

FROM \"tablename\"

"; "" ; "" )

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

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

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.