SELECT \"DayOfWeek\", COUNT(\"DayOfWeek\")
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.
DayOfWeek is a name of function, so I don't prefer to use as field name.
That worked to a degree, but I get counts for every variation of the day, ex.,
And I need these Sum's for a chart/graph, so all data in one filed probably won't work.
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
GROUP BY \"addedField\"
"; "" ; "" )
This can be data source of chart. X axis is static text calculation as "Sun¶Mon¶Tue¶Wed¶Thu¶Fri¶Sat"
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.
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.