How can I get the count in SQL?

How to get an average per day on MS Access using SQL?

• Hi, I am using access SQL to create a query, and this is my code: Select Weekday (date_Entered) AS [Day of Week], count(*) AS [Total Users], -int(-count(*)/DateDiff("ww", date_entered, now())) AS [average] FROM Pool_Usage GROUP BY Weekday (date_Entered), DateDiff("ww", date_entered, now()) UNION SELECT "TOTAL" AS [Day of Week], count(*) AS [Total Users], "" AS [average] FROM Pool_Usage; The -int(-count(*)/DateDiff("ww", date_entered, now())) is done this way as the round function uses bankers rounding and not arithmetic. When I try to run the query, I would like it to be grouped by each day. This works if I run the query without the "-int(-count(*)/DateDiff("ww", date_entered, now())) AS [average]" and do not group it by " DateDiff("ww", date_entered, now())", so this works: Select Weekday (date_Entered) AS [Day of Week], count(*) AS [Total Users] FROM Pool_Usage GROUP BY Weekday (date_Entered) UNION SELECT "TOTAL" AS [Day of Week], count(*) AS [Total Users] FROM Pool_Usage; Is there a way to make it work so I can still group it by each day of the week? Thanks

• Answer:

  I'm not sure what you're trying to accomplish with your 'average' computation, but try using subqueries (perhaps applying your computation against the AVG function): SELECT weekDay AS "Day Of Week", AVG(usage) AS "Average Users" FROM (SELECT Weekday(date_entered) AS weekDay, SUM(usage) AS usage FROM (SELECT date_entered, COUNT(*) AS usage FROM Pool_Usage GROUP BY 1) GROUP BY 1) GROUP BY weekDay Note - this assumes that you had at least one user on any given day...if not, you'll have to do a UNION to make sure the days with no usage get included to produce an accurate average .