Daily Average with Grouped Dates 1

[ychousa]

Hi. I have a table with Lead_ID and Date.

I made a query to get total leads per day. This is the query:
SELECT MAIN.Date, Count(MAIN.Date) AS Total
FROM MAIN
GROUP BY MAIN.Date;

The result looks like this:
Date Total
7/16/2003 6
7/17/2003 9
7/18/2003 12
:

What I want to get average leads per day something like:
Total Leads Total Date Counted Average lead per day
55 5 11

I tried to modify above SQL statement, but because of GROUP BY clause, it only gives me aggregated totals per each date like above.

How can I get the result?

Thanks in advance!

 

[rac2]

Wont you be specifying a range of dates for these statistics?

SELECT MAIN.Date, Count(MAIN.Date) AS Total
FROM MAIN
WHERE MAIN.Date BETWEEN [dateBegin] AND [dateEnd]
GROUP BY MAIN.Date;

Could you not therefore calculate the number of dates as

... DateDiff("d",[dateBegin],[dateEnd]) ...

And put it all together like this-

SELECT
       Count(MAIN.Date) AS Total,
       DateDiff("d",[dateBegin],[dateEnd]) AS TotalDates,
       Count(MAIN.Date)/DateDiff("d",[dateBegin],[dateEnd]) AS DailyAverage
FROM MAIN
WHERE MAIN.Date BETWEEN [dateBegin] AND [dateEnd]

 

[ychousa]

Hi, rac2.

Thank you so much about your post.

That's exactly what I wanted!

I've not known about DateDiff function. I think I need to explore more about that.

Thanks again and have a great day!

John

 

[PROXI]

I know that this thread has been dead for a while... but I was wondering if anyone knows how to exclude the weekeds in this sql statement so that if I do a count to get a daily average that is accurate.


Thanks,

PROXI

 

[rac2]

Adding this condition to the WHERE clause would get rid of any leads developed on Saturday and Sunday.

...
WHERE MAIN.Date BETWEEN [dateBegin] AND [dateEnd]
  AND Weekday(MAIN.Date) NOT IN (1,7)

But you seem to imply that there are no leads on the weekend so that is not the answer.

We need to count the number of days between dateBegin and dateEnd that are not weekend days. That particular number could be obtained from

SELECT COUNT(*)
FROM (
      SELECT DISTINCT MAIN.Date
      FROM MAIN
      WHERE Weekday(MAIN.Date) NOT IN (1,7)
      AND Weekday(MAIN.Date) NOT IN (1,7)
)

So you could try substituting this query for the DateDiff value.

SELECT
       Count(MAIN.Date) AS Total,
       DateDiff("d",[dateBegin],[dateEnd]) AS TotalDates,
       Count(MAIN.Date)/(
          SELECT COUNT(*)
          FROM (
                SELECT DISTINCT MAIN.Date
                FROM MAIN
                WHERE Weekday(MAIN.Date) NOT IN (1,7)
                AND MAIN.Date BETWEEN [dateBegin] AND [dateEnd]
          )
       AS DailyAverage
FROM MAIN
WHERE MAIN.Date BETWEEN [dateBegin] AND [dateEnd]

I have not verified that this will work in Access, it is a bit complex. In my work with web pages and SQL Server I would tend to take a different approach, obtaining the number of weekdays and total leads from separate queries. Then calculate the average and arrange the results as a row for display in the script that generates the web page. In Access this approach would use the report design functions.

 

[PROXI]

SELECT Count(new_main_info.kioskDate) AS Total, 
DateDiff("d",[Enter Start Date:],[Enter End Date:]) AS TotalDates, 
Count(new_main_info.kioskDate)/DateDiff("d",[Enter Start Date:],[Enter End Date:]) AS DailyAverage
FROM New_main_info
WHERE (((New_main_info.KioskDate) Between [Enter Start Date:] And [Enter End Date:]));

This is the code that I have right now. When I tried to add the above code to modify it, I just got a bunch of syntax errors. I played with it for a while but I couldnt figure it out. Do you have any idea what else I can do, or try to splice this with yours and let me know?

Thanks,

PROXI

 

[rac2]

Oh well, a bunch of syntax errors! I know what caused those.

Try making three queries, one for the total leads, one for the number of weekdays, and one that brings the two numbers together. This simplifies the syntax and may facilitate finding the syntax errors.

Here are examples against one of my Access tables.
How many dollars - this query is saved with the name WeekdayCounterTotal

SELECT SUM([tblaccounts].[Amount_Paid]) AS Expr1000
FROM tblaccounts
WHERE (((Weekday([tblaccounts]![Date_of_Payment])) NOT IN (1,7)
  AND [tblaccounts]![Date_of_Payment] BETWEEN [Enter Start Date: ] AND [Enter End Date:]));

How many weekdays - this one is saved with the name WeekdaysCounter.

SELECT Count(*) AS Expr1
FROM [SELECT DISTINCT tblaccounts.Date_of_Payment
FROM tblaccounts
WHERE (((Weekday([tblaccounts]![Date_of_Payment])) NOT IN (1,7)
    AND [tblaccounts]![Date_of_Payment] BETWEEN [Enter Start Date: ] AND [Enter End Date:]))
]. AS TotalPaid;

The final answer!

SELECT [WeekdayCounterTotal].[Expr1000],
       [WeekdaysCounter].[Expr1],
       [WeekdayCounterTotal].[Expr1000]/[WeekdaysCounter].[Expr1]
FROM WeekdayCounterTotal, WeekdaysCounter;

These run on my computer.