SQL — Count (*) using SUM CASE with multiple date ranges and WHERE conditions.

There’s an easy way without using pivots!

The Problem

I would like to select multiple counts from the same table with different WHERE conditions based on date ranges. Our desired outcome is four columns.

• This Week: Count of products ordered in the last week
• This Month: Count of products ordered in the last month
• Previous Year: Count of products ordered in the last year
• All Time: Count of all of the products ordered

Here is the sample data that we will be using:

Sample Data

Our expected output should be:

When I first started writing the query I quickly ran into an issue. The WHERE condition (Where we would be putting our date filters) is at the end of the query and applies to all columns.

Regardless I quickly wrote some queries to get the data I wanted and ended up with four queries… not the best solution but let’s take a quick look at one of them: