How to Summarize SQL Records by Day

URL:
http://www.jessespevack.com/blog/2017/9/9/how-to-summarize-sql-records-by-day

How to Summarize SQL Records by Day

Some readers of this blog have been asking about useful SQL queries that have come up in my current job. So I wanted to share a query that is not super complicated, but very useful. 

The use case for this query is when you are looking to view a summary of records by day. For example, maybe you have many invoices everyday, but would like to track total invoice revenue by day.
First, let's figure out how to get the total revenue for a specific day. To do so we can use something like:

SELECT SUM(total) FROM invoices
  WHERE created_at BETWEEN '2017-08-24 00:00:00' AND '2017-08-24 23:59:59';

The above query will add up the total attribute on each invoice created on August 8, 2017. So now we know how much revenue we generated on this date, but what if I want to find revenue for every day this month?
One option would be to just run this query thirty times and increment the date manually each time. Obviously there is a better way.
First, lets expand the scope of our query to include the entire month of August. We also need to select the created_at attribute, which we will call 'date', so that our query will return two columns - date and total. Finally we will use the CAST function to convert our created_at DateTime attribute to Date.

SELECT CAST(created_at AS DATE), SUM(total) AS total_sales FROM invoices
  WHERE created_at BETWEEN '2017-08-01 00:00:00' AND '2017-09-01 00:00:00'

Next we are going to group our results by date. If we had not casted our created_at attribute to date in the last step, grouping by date would group by DateTime down to the second, which is not what we want in this case.

SELECT CAST(created_at AS DATE), SUM(total) AS total_sales FROM invoices
  WHERE created_at BETWEEN '2017-08-01 00:00:00' AND '2017-09-01 00:00:00'
  GROUP BY CAST(created_at AD DATE)

This will return the summary we want, something like:

If we want to order the query results by date (or sales, to see our best day) we can add an order clause to the query:

SELECT CAST(created_at AS DATE), SUM(total) AS total_sales FROM invoices
  WHERE created_at BETWEEN '2017-08-01 00:00:00' AND '2017-09-01 00:00:00'
  GROUP BY CAST(created_At AS DATE)
  ORDER BY 1;

The 1 in the order clause refers to the first column. This will give us something like: