Site icon Parenting Shack

get sum of distinct records and compare between two date ranges

I have a table called purchases, with columns: name, amount, city, date.

I want to return the sum of amount and order it by city with most sum of amount, and compare two date ranges.

Here is a fiddle: http://sqlfiddle.com/#!9/6b2017/6

I can get the first set of cities with their sum(amount) in order of sum(amount) desc (period 1):

select distinct city, sum(amount) as total
from purchases
where date between '2020-07-01' and '2020-10-31'
group by city
order by sum(amount) desc
limit 3

The query above returns cities: Tulsa, New York, Chicago

but I also want to get in the same query, the previous 3 months, but with the same cities that were returned in the previous query, something like this, but like I mentioned, I want it returned in just one query (period 2)

select city, sum(amount) as total
from purchases
where city in ('Tulsa', 'New York', 'Chicago')
  and date between '2020-03-01' and '2020-06-31'
group by city

Looking to return the cities (in the order they are in the first query), with sum(amount) per each date range, so in theory:

Tulsa period 1: sum(amount) Tulsa period 2: sum(amount) New York period 1: sum(amount) New York period 2: sum(amount) Chicago period 1: sum(amount) Chicago period 2: sum(amount)

Any help is appreciated

Exit mobile version