How can I find out same values with more than one condition and put values of the same column in dif-CodePudding

I have to find out in which pair of countries is the same number of sales at the same date and the same seller. Then I want to order it such as there are the two countries from the same column next to each other in separate columns, as well as date, the seller and the number of sales.

I have a table countries like this:

Country_code	Country_name
1	Argentina
2	Brazil
3	Peru
4	Bolivia

And I have a (shortened) table sales like this:

Country_code	Date	No_of_sales	Seller
1	2021-01-01	17	D
2	2021-01-01	48	K
3	2021-01-01	19	X
4	2021-01-01	22	Z
1	2021-02-01	66	D
2	2021-02-01	66	D
3	2021-02-01	87	K
4	2021-02-01	10	K
1	2021-03-01	27	Z
2	2021-03-01	17	D
3	2021-03-01	17	D
4	2021-03-01	32	D

So, in this case, my expected result should be:

Date	Country_name_1	Country_name_2	Seller	No_of_sales
2021-02-01	Argentina	Brazil	D	66
2021-03-01	Brazil	Peru	D	17

I already managed it to select the correct data, but I didn't manage to separate the countries in two different columns.

This is my code:

SELECT s1.date, c.country_name, s1.seller, s1.No_of_sales
FROM (sales s1
INNER JOIN
    (SELECT seller, date, No_of_sales FROM sales
    GROUP BY seller, date, No_of_sales
    HAVING COUNT(seller) > 1) s2
ON s1.seller = s2.seller AND s1.date = s2.date AND s1.No_of_sales = s2.No_of_sales)
JOIN countries c
ON s1.Country_code = c.Country_code

Thank you in advance!

CodePudding user response：

OK. Here is a suggested solution considering that only pairs of countries are expected/required and only two country name columns are displayed. By using min and max values in the grouping, only these two countries will be selected.
(If there happen to be more than two countries with the same date, seller, no_of_sales then the middle ones will be ignored.)

SELECT distinct s1.Date
,c1.country_name Country_name_1
,c2.country_name Country_name_2
,s1.Seller, s1.No_of_sales 
FROM (sales s1
INNER JOIN
(SELECT min(country_code) country1, max(country_code) country2, Seller, [Date], No_of_sales 
FROM sales
GROUP BY Seller, [Date], No_of_sales
HAVING COUNT(Seller) > 1) s2
ON s1.Seller = s2.Seller AND s1.Date = s2.Date 
AND s1.No_of_sales = s2.No_of_sales)
JOIN countries c1
ON s2.country1 = c1.Country_code
JOIN countries c2
ON s2.country2 = c2.Country_code

CodePudding user response：

other solution but is much complicated comparing with the solution offered by Catherine

            with flo as (
            select *,  row_number()over(order by (select 1)) -row_number()over(partition by date, no_of_sales, seller order by (select 1)) as rn
            from sales), 
            flo1 as (
            select country_code, date, no_of_sales, seller from (
            select country_code, date, no_of_sales, seller, rn ,lead(rn)over(order by rn) as ln
            from flo ) a
            where ln-rn=0 or ln-rn=2
            ) ,
            flo2 as (select a.date, b.country_name, b.country_name as bcountry_name, a.no_of_sales, a.seller
            from flo1 a join countries b on  a.country_code=b.country_code
            ), 
            flo3 as (select *, row_number()over(partition by date, no_of_sales, seller order by country_name)as rn 
            from flo2)
            select date, max(case when rn=1 then country_name end) as country_name, 
            max(case when rn=2 then bcountry_name end) as bcountryname, no_of_sales, seller
            from flo3 
            group by no_of_sales, seller, date
            order by 1