PHP - SQL - Day of Week Averages

I currently track the number of items that are bought at a store each day (ItemCount).  I also track the date for each (CreatedOn).

I am working to create a dashboard for management to look at to quickly get an idea of how the store is doing at any given time.  I currently am showing today's total ItemCount - but would like to provide more meaningful details.

Would it be possible to create a query (or multiple queries) that shows me today's count (Monday's Total Item Count) as well as the average for all Monday's, the daily average, the YTD Daily Average, and the Monday Max Item Count (the highest ItemCount on any Monday)

I haven't been able to figure out any kind of efficient query to do this.

I am using PHP to query a SQL Server 2008 database.

I appreciate any help!
LVL 1
Matthew RoessnerSenior Systems ProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Take a look at the SQL Server DATEPART function
https://msdn.microsoft.com/en-us/library/ms174420.aspx

With these you can do things like

SELECT sum(sales), DATEPART(weekday, saledate) FROM table GROUP BY DATEPART(weekday, saledate)

Open in new window


All the queries you want are a variation on this theme. You group the results by the date part you want and potentially add filters (WHERE Clause) to limit date ranges etc.
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
I was able to put this query together:

SELECT COUNT(*) as TotalSales, DATEPART(dw, CreatedOn) as DayofWeek FROM TableName where DATEPART(dw, CreatedOn)='3' GROUP BY DATEPART(weekday, CreatedOn)

From this, I get the following:

TotalSales      DayofWeek
87020               3

This is a step closer to where I want to be - THANKS!

From here - is there an easy way to find the total number of "Day 3's" in the table?   I am assuming that this is giving me the total count of all transactions that are found on Tuesdays.  Since I wanted to get the average - I need to divide my 87020 by the total number of Tuesday's in my table.  Is there a way to do this as part of this query - or even in a secondary query...?

I appreciate your help!
Julian HansenCommented:
A couple of questions
TotalSales - is this total number of sales records - or total of sales (COUNT vs SUM)
is there an easy way to find the total number of "Day 3's" in the table?
How is this different from what you have already calculated with the query you posted?

As to the average you would need to do something like this (Refer AVG function https://msdn.microsoft.com/en-za/library/ms177677.aspx)
SELECT AVG(TotalSales), weekd FROM (
    SELECT 
      COUNT(*) AS TotalSales, 
      DATEPART(year, CreatedOn) AS year, 
      DATEPARTmonth, CreatedOn) AS month, 
      DATEPART(day, CreatedOn) AS day, 
      DATEPART(dw, CreatedOn) AS weekd
    FROM TableName 
    GROUP BY 
      DATEPART(year, CreatedOn), 
      DATEPART(month, CreatedOn), 
      DATEPART(day, CreatedOn), 
      DATEPART(weekday, CreatedOn)
) A
GROUP BY weekd;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
The TotalSales is a count. I essentially just want to know how many TRANSACTIONS we had for any given day.

And ultimately I want to say, What are the average number of transactions that occur on Tuesdays.  Also - what is the MAX number of transactions that have ever occurred on a Tuesday.

Thanks in advance for your help
Julian HansenCommented:
What are the average number of transactions that occur on Tuesdays.
The previous post should do this.
To get the MAX you can do the same query but change AVG to MAX
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
Thanks for your help!
Julian HansenCommented:
You are welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.