SQL & PL/SQL

Hi All,

I am working on a query to get the  Number of days that has elapsed since the order_date, group it into a bucket ( sorry for the lack of better word) of range and count how many item would be into each bucket. It may sound confusing, but let me provide an example.

For simplicity, say there is a table - TEMPA which stores information about an item.

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

CREATE TABLE TEMPA ( ITEM VARCHAR2(1) , LINE_KEY NUMBER(10), ORDER_DT DATE) 

Insert Script -

INSERT INTO TEMPA ( 
SELECT 'A', 1, TO_DATE('11-07-18', 'DD-MM-YY') FROM DUAL 
UNION ALL
SELECT 'A', 2, TO_DATE('30-08-07', 'DD-MM-YY') FROM DUAL 
UNION ALL
SELECT 'B', 1, TO_DATE('01-09-07', 'DD-MM-YY') FROM DUAL 
UNION ALL
SELECT 'C', 1, TO_DATE('11-07-18', 'DD-MM-YY') FROM DUAL 
UNION ALL
SELECT 'C', 2, TO_DATE('18-06-18', 'DD-MM-YY') FROM DUAL 
UNION ALL
SELECT 'C', 3, TO_DATE('11-06-18', 'DD-MM-YY') FROM DUAL 
UNION ALL
SELECT 'C', 4, TO_DATE('10-06-18', 'DD-MM-YY') FROM DUAL 
UNION ALL
SELECT 'D', 1, TO_DATE('26-07-18', 'DD-MM-YY') FROM DUAL 
);
COMMIT;

Now, what I am working on is to populate the range for Days Elapsed ( sysdate - Order_dt) into four buckets ( Order date less than or equal to 10 days, between 11 to 20 days, between 21 to 30days and 30 or more days). Then I will need to do the count on each bucket.

This is what I have so far -

SELECT ITEM, LINE_KEY, 
  CASE WHEN TRUNC(SYSDATE - ORDER_DT) <= 10 THEN 1
       ELSE 0
       END AS LessThanTenBucket,
  CASE WHEN  TRUNC(SYSDATE - ORDER_DT)>10 AND TRUNC(SYSDATE - ORDER_DT) <=20  THEN 1
       ELSE 0
       END AS TenAndTwentyBucket,
  CASE WHEN TRUNC(SYSDATE - ORDER_DT)>20 AND TRUNC(SYSDATE - ORDER_DT) <=30 THEN 1
       ELSE 0
       END AS TwentyAndThirtyBucket, 
  CASE WHEN TRUNC(SYSDATE - ORDER_DT) > 30 THEN 1
       ELSE 0
       END AS ThirtyAndUpBucket     
FROM TEMPA

This Query results following output -

ITEM   LINE_KEY LESSTHANTEN TENANDTWENTY TWENTYANDTHIRTY THIRTYANDUP
---- ---------- ----------- ------------ --------------- -----------
A             1           1            0               0           0 
A             2           0            0               0           1 
B             1           0            0               0           1 
C             1           1            0               0           0 
C             2           0            0               1           0 
C             3           0            0               1           0 
C             4           0            0               0           1 
D             1           1            0               0           0 
 8 rows selected 

     What I am looking for is sql query that will give me following result. I would have to make sure the query would run in MS SQL as well.

CountofLessThan10Bucket    CountOfTenAndTwentyBucket    CountofTwentyAndThirtyBucket    CountofThirtyandUpBucket
      3                      0                                  2                          3

I would appreciate if someone can guide me to the right direction. Thank you!!