Reporting SQL Help
I need to create a repot to show the sale of 3 items cars, bikes and books in last week , month , year and all time in a tablular format . I am posting a sample DDL and sample output below. Can you please help me with a SQL for the same?
CREATE TABLE DAS_DESC.BOOKS
(
SALE_DT DATE
)
/
SET DEFINE OFF;
Insert into BOOKS
(SALE_DT)
Values
(TO_DATE('5/14/2020 7:30:58 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BOOKS
(SALE_DT)
Values
(TO_DATE('5/12/2020 7:31:04 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BOOKS
(SALE_DT)
Values
(TO_DATE('5/7/2020 7:31:08 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BOOKS
(SALE_DT)
Values
(TO_DATE('5/6/2020 7:31:09 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BOOKS
(SALE_DT)
Values
(TO_DATE('4/24/2020 7:31:18 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BOOKS
(SALE_DT)
Values
(TO_DATE('3/25/2020 7:31:22 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
CREATE TABLE DAS_DESC.BIKES
(
SALE_DT DATE
)
/
SET DEFINE OFF;
Insert into BIKES
(SALE_DT)
Values
(TO_DATE('5/14/2020 7:31:35 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BIKES
(SALE_DT)
Values
(TO_DATE('5/13/2020 7:31:37 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BIKES
(SALE_DT)
Values
(TO_DATE('5/12/2020 7:31:39 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BIKES
(SALE_DT)
Values
(TO_DATE('5/10/2020 7:31:41 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BIKES
(SALE_DT)
Values
(TO_DATE('5/7/2020 7:31:43 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BIKES
(SALE_DT)
Values
(TO_DATE('5/4/2020 7:31:46 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into BIKES
(SALE_DT)
Values
(TO_DATE('4/24/2020 7:31:50 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
CREATE TABLE DAS_DESC.CARS
(
SALE_DT DATE
)
/
SET DEFINE OFF;
Insert into CARS
(SALE_DT)
Values
(TO_DATE('5/14/2020 7:32:16 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into CARS
(SALE_DT)
Values
(TO_DATE('5/13/2020 7:32:20 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into CARS
(SALE_DT)
Values
(TO_DATE('5/11/2020 7:32:22 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into CARS
(SALE_DT)
Values
(TO_DATE('5/8/2020 7:32:24 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into CARS
(SALE_DT)
Values
(TO_DATE('5/4/2020 7:32:26 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
Samle Output
Item Count_sold_this_week Count_sold_this_month count_sold_this_year count_sold_all_time
Books 0 2 3 4
Cars 1 2 3 5
Bikes 0 1 1 2
ALL 1 5 7 11