SQL: How to deal with NULL and PARTITION BY?-CodePudding

I've got a question, if you don't mind terribly. So suppose I have this kind of a table here – Products (amount sold by quarter in 2000, only there are multiple entries for the same product and quarter (with different dates)):

product	quarter	amount sold
Jeans	1	20
Jeans	2	40
Jeans	3	60
Jeans	4	5
Skirt	1	10
Skirt	2	5
Skirt	3	30
Blouse	1	15
Blouse	2	40
Blouse	3	60
Blouse	4	15

I want to reintroduce it as follows:

product	quarter1	quarter2	quarter3	quarter4
Jeans	20	40	60	5
Skirt	10	5	30	Null
Blouse	15	40	60	15

I decided to do it with partition (cause it's not exactly that simple, there are different rows with the same quarter for the same product, but different amount sold, that's why it's sum(amount_sold), but you get the idea, I hope):

WITH quater_sales as(
SELECT DISTINCT pro.product, pro.quarter, to_char (sum(pro.amount_sold) OVER (PARTITION BY pro.product, pro.quarter)) AS quater
FROM products pro
ORDER BY pro.pro.product)
SELECT quater_sales.prod_product, quater_sales.quater AS "Q1", qu2.quater AS "Q2", qu3.quater AS "Q3", qu4.quater AS "Q4"
FROM quater_sales
 JOIN quater_sales qu2 ON quater_sales.prod_subcategory=qu2.prod_subcategory
 JOIN quater_sales qu3 ON quater_sales.prod_subcategory=qu3.prod_subcategory
 JOIN quater_sales qu4 ON quater_sales.prod_subcategory=qu4.prod_subcategory
WHERE quater_sales.calendar_quarter_number=1 and qu2.calendar_quarter_number=2 and qu3.calendar_quarter_number=3 and qu4.calendar_quarter_number=4

The problem is with partition (or maybe it's the condition of select) that the product that was not sold in all the 4 quarters is just discarded. What I basically get in the end is this:

product	quarter1	quarter2	quarter3	quarter4
Jeans	20	40	60	5
Blouse	15	40	60	15

So how do I make "skirts" appear there too? I am a bit stuck with this.

CodePudding user response：

Have you considered using a PIVOT statement?

WITH
    quarter_sales (product, quarter, amount_sold)
    AS
        (SELECT 'Jeans', 1, 20 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 2, 40 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 3, 60 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 4, 5 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 1, 10 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 2, 5 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 3, 30 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 1, 15 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 2, 40 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 3, 60 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 4, 15 FROM DUAL)
SELECT *
  FROM (SELECT *
          FROM quarter_sales qs)
       PIVOT (SUM (amount_sold)
             FOR quarter
             IN (1 AS quarter1, 2 AS quarter2, 3 AS quarter3, 4 AS quarter4));


   PRODUCT    QUARTER1    QUARTER2    QUARTER3    QUARTER4
__________ ___________ ___________ ___________ ___________
Blouse              15          40          60          15
Jeans               20          40          60           5
Skirt               10           5          30

CodePudding user response：

try pivot. this is how you would pivot in tsql

declare @tmp as table(product varchar(20),quarter int,[amount sold] int);

insert into @tmp values
('Jeans',   1,  20)
,('Jeans',  2,  40)
,('Jeans',  3,  60)
,('Jeans',  4,  5)
,('Skirt',  1,  10)
,('Skirt',  2,  5)
,('Skirt',  3,  30)
,('Blouse', 1,  15)
,('Blouse', 2,  40)
,('Blouse', 3,  60)
,('Blouse', 4,  15)


    select  product, [1] as quarter1,[2] as quarter2,[3] as quarter3,[4] as quarter4
from
(
select product,quarter,[amount sold] from @tmp)p
pivot
(
sum([amount sold])
for quarter in([1],[2],[3],[4])
) as pvt

output:

product quarter1    quarter2    quarter3    quarter4
Blouse  15          40          60          15
Jeans   20          40          60          5
Skirt   10          5           30          NULL