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