I need to have total count of each of the product categories down below (food, drinks, others) and compare today’s value with yesterday’s, ordered firstly by date and then by numbered categories (food,drinks,others). In this example I just put these dates, but I need a flexible solutions so it keeps comparing today with yesterday.
I have a table with the following format.
Date | Product_category | Product_name |
---|---|---|
2023-01-01 | food | 1 |
2023-01-01 | drinks | 45 |
2023-01-01 | food | 3 |
2023-01-01 | others | 12 |
2023-01-02 | others | 6 |
2023-01-02 | others | 53 |
2023-01-02 | drinks | 62 |
I want the following result
Date | Product_category | Total_count | Daily_difference |
---|---|---|---|
2023-01-02 | 1.food | 0 | -2 |
2023-01-02 | 2.drinks | 2 | 0 |
2023-01-02 | 3.others | 2 | 1 |
I tired the following query, but unfortunately it didn’t work the way I need it to. It keeps telling me the Date doesn’t exist. How can I fix it using standard sql?
<code>with A as
(
with W as
(
select
Date,
product_category,
count(product_category) as Total_count
from
table1
group by
1, 2
)
select distinct(case
when Q.Product_category = food then '1.food'
when Q.Product_category = drinks then '2.drinks'
when Q.Product_category = others then '3.others'
end as Numbered_categories,
Q.Date,
Total_count
from
table1 as Q
inner join
W on W.Product_category = Q.Product_category
order by
1, 2
)
select
A2.Date,
A2.Product_category,
A2.Total_count,
(A1.Total_count - A2.Total_count) as Daily_difference
from
A as A1
inner join
A as A2 on A1.date = A2.date - 1
order by
1, 2
</code>
<code>with A as
(
with W as
(
select
Date,
product_category,
count(product_category) as Total_count
from
table1
group by
1, 2
)
select distinct(case
when Q.Product_category = food then '1.food'
when Q.Product_category = drinks then '2.drinks'
when Q.Product_category = others then '3.others'
end as Numbered_categories,
Q.Date,
Total_count
from
table1 as Q
inner join
W on W.Product_category = Q.Product_category
order by
1, 2
)
select
A2.Date,
A2.Product_category,
A2.Total_count,
(A1.Total_count - A2.Total_count) as Daily_difference
from
A as A1
inner join
A as A2 on A1.date = A2.date - 1
order by
1, 2
</code>
with A as
(
with W as
(
select
Date,
product_category,
count(product_category) as Total_count
from
table1
group by
1, 2
)
select distinct(case
when Q.Product_category = food then '1.food'
when Q.Product_category = drinks then '2.drinks'
when Q.Product_category = others then '3.others'
end as Numbered_categories,
Q.Date,
Total_count
from
table1 as Q
inner join
W on W.Product_category = Q.Product_category
order by
1, 2
)
select
A2.Date,
A2.Product_category,
A2.Total_count,
(A1.Total_count - A2.Total_count) as Daily_difference
from
A as A1
inner join
A as A2 on A1.date = A2.date - 1
order by
1, 2