SQL multiple WHERE conditions for multiple columns-CodePudding

I am trying to create the following table:

ID	Name	SalesDate	MaxNikeSales	MaxAdidasSales	SalesDifference (Nike - Adidas)
1	John	2022-01-01	94343	23453	70890
2	John	2022-01-02	23456	0	23456
3	John	2022-01-03	54678	0	54678
4	John	2022-01-04	12310	43345	-31035
5	John	2022-01-05	0	94575	-94575

I am trying to create the table using the WHERE clause, but am not sure how to get both brands to show as fields in the same table.

The data I am working with is structured as follows:

Name	Brand	Sales	SalesDate
John	Nike	13344	2022-01-01
John	Adidas	23453	2022-01-01
John	Nike	94343	2022-01-01
John	Nike	23456	2022-01-02
John	Nike	54678	2022-01-03
John	Nike	23643	2022-01-03
John	Nike	12310	2022-01-04
John	Adidas	43345	2022-01-04
John	Adidas	94575	2022-01-05
John	Adidas	23451	2022-01-05

I would really appreciate any assistance on this.

CodePudding user response：

You can use max() with case clause to calculate what you need. Group the data by SalesDate and you have everything you need.

select row_number() over(order by SalesDate) as ID
       , max(Name)
       , SalesDate
       , max(case when Brand = 'Nike' then Sales else 0 end) MaxNikeSales
       , max(case when Brand = 'Adidas' then Sales else 0 end) MaxAdidasSales
       , max(case when Brand = 'Nike' then Sales else 0 end) - 
         max(case when Brand = 'Adidas' then Sales else 0 end) 
         as "SalesDifference (Nike - Adidas)"
from mytable
group by SalesDate

Here is a demo

CodePudding user response：

I think "MAX(name)" is wrong under different Names. Why is maximum Sales? They may be counted by brand on the same day.