-1

Oracle SQL Developer noob here. I am trying to create a quarterly sales analysis based on warehouse names and output the Quarterly sales as Q1, Q2 etc. Using Drill down query concepts.

I am unsure if what I am doing is in any way related to a Drill Down concept but this is one of my many attempts. I am hoping for a way to remove the null value outputs to be left with proper data.

It is in the hopes that with the removal of the null data, all the outputs related to specific warehouse names will also all output to 1 line. Leaving me with Warehouse_Name(1), Q1 Data, Q2 Data, etc

I am currently using two tables for this query which are Warehouse: Warehouse_id, warehouse_name and quantity_sold Time_Period: Date_id, Full_date, Days, Month_short and year.

My Code is as follows:

SELECT TO_CHAR(Full_date, 'Q') AS MY_QTR, 
    Sum(Quantity_sold) AS HOW_MANY_SOLD_PER_QTR
FROM warehouse, Time_Period 
GROUP BY TO_CHAR(Full_date, 'Q')
ORDER BY 1;
Select warehouse_Name,
case 
when TO_CHAR(Full_date, 'Q') = 1
then Sum(Quantity_sold) 
End as Q1_2019,
case 
when TO_CHAR(Full_date, 'Q') = 2
then Sum(Quantity_sold)
End as Q2_2019,
case 
when TO_CHAR(Full_date, 'Q') = 3
then Sum(Quantity_sold)
End as Q3_2019,
case 
when TO_CHAR(Full_date, 'Q') = 4
then Sum(Quantity_sold)
End as Q4_2019
FROM warehouse w1, Time_Period t1
where Q1_2019 IS NOT NULL
GROUP BY warehouse_Name,TO_CHAR(Full_date, 'Q')
ORDER BY 1;

Which provides me with an output of

Waarehouse_Name   Q1   Q2   Q3   Q4
--------------- ---- ---- ---- ----
Henderson        990 Null Null Null    
Henderson       Null 1001 Null Null
Henderson       Null Null 1012 Null
Henderson       Null Null Null 1012
DalCipher
  • 9
  • 2

1 Answers1

2

Edit :

As mentioned by @mathguy

“He was using conditional SUM (conditional by quarter) and also was grouping by quarter in the GROUP BY clause. If you remove the quarter from GROUP BY (which you eventually did), there would be no more null in the output already. The main point of the answer has noting to do with adding else 0 to the case expressions. “

So using else here is unnecessary Removing quarter from GROUP BY does the thing.

Select warehouse_Name,  SUM( case when TO_CHAR(Full_date, 'Q') = 1 then Quantity_sold else 0 End) as Q1_2019,
                        SUM( case when TO_CHAR(Full_date, 'Q') = 2 then Quantity_sold else 0 End) as Q2_2019,
                        SUM( case when TO_CHAR(Full_date, 'Q') = 3 then Quantity_sold else 0 End) as Q3_2019,
                        SUM( case when TO_CHAR(Full_date, 'Q') = 4 then Quantity_sold else 0 End) as Q4_2019
FROM warehouse w1
where Full_date IS NOT NULL
GROUP BY warehouse_Name
ORDER BY 1;
Aman Singh Rajpoot
  • 1,451
  • 6
  • 26
  • When you mean "If you want only one row then use an aggregate function like min or max for each column." How would one code that? Would I have to put it as SUM(MAX(Quantity_Sold))? Or some otherway? – DalCipher Apr 25 '21 at 03:24
  • Updated answer. – Aman Singh Rajpoot Apr 25 '21 at 03:44
  • Great Scotts man, you are an absolute hero. Thank you for your help mate. I have been scratching my head over this. Now I have some reading up to do based on your answer. So that I can fully understand everything you've done. Thank you once more. – DalCipher Apr 25 '21 at 04:02
  • Wouldn't hurt if you accepted the answer, then, @DalCipher, so that people know that this problem is *solved*. – Littlefoot Apr 25 '21 at 05:25
  • You updated the answer, that's fine. But the first sentence in the answer still says "you can use the `else` clause" when, in fact, the main change is removing the quarter from the `group by` clause. You may want to clarify that too. –  Apr 25 '21 at 12:44
  • He wanted the output rows without nulls and using the wrong way to calculate the sum of quantity sold for each quarter. – Aman Singh Rajpoot Apr 25 '21 at 12:50
  • He was using conditional SUM (conditional by quarter) and also was grouping by quarter in the `GROUP BY` clause. If you remove the quarter from `GROUP BY` (which you eventually did), there would be no more `null` in the output already. The main point of the answer has noting to do with adding `else 0` to the `case` expressions. The OP thought it would; he was mistaken. Don't encourage that - the point of the answer is (or "should be", anyway) the wrong `group by` clause in his attempt. –  Apr 25 '21 at 12:53
  • Got your point, I was misunderstood , I will fix it. – Aman Singh Rajpoot Apr 25 '21 at 13:38