Hi All,
As per the attache files or below specified table DDL, DML and SQL, I need to
get below details:
Get Order_ID, Order_Date, Salesman_ID for different Salesmen :1. Who sold
maximum Copy for a day2. Who sold maximum amount of book for a day3. Who got
maximum order for a day
As per below SQL, I am able to fetch the result only in one record that is
correct. But I need to get the data for every single date i.e. for all distinct
date only, i.e. Date should not repeat in the row, which satisfy above three
condition. The result could be for different Salesmen or same Salesman
depending on the data in the table.
So, I need to get data for every distinct date in the table. Please help how to
get it.
One option I see to put "Order_ID" column in Max() function to avoid Group By
issue.
Kindly suggest, If possible please correct the above SQL and share.
Thanks In Advance.
With Best Regards.
Vishal
--------------------------------------------------------------------
DDL:
CREATE TABLE ORDERED_BOOK_DETAILS( ORDER_ID Varchar(50) NOT NULL, ORDER_DATE
Date NOT NULL, SALESMAN_ID Varchar(10) NOT NULL, BOOK_NAME Varchar(50) NOT
NULL, BOOK_PRICE Numeric(15,2) DEFAULT 0, COPY_SOLD Integer DEFAULT 0,
TOTAL_AMOUNT Numeric(15,2) DEFAULT 0, PRIMARY KEY
(ORDER_ID,ORDER_DATE,SALESMAN_ID,BOOK_NAME));
DML:
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001',
'The Immortals of Meluha', '250.00', '50', '12500.00');INSERT INTO
ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE,
COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 'The Secret of
The Nagas', '300.00', '50', '15000.00');INSERT INTO ORDERED_BOOK_DETAILS
(ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD,
TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 'Oath of The Vayuputras',
'350.00', '50', '17500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID,
ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES
('ORD002', '01.01.2010', 'S002', 'The Magic of Thinking Big', '400.00', '400',
'160000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE,
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD003',
'01.01.2010', 'S003', 'The Immortals of Meluha', '250.00', '10',
'2500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID,
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD004', '01.01.2010',
'S003', 'The Secret of The Nagas', '300.00', '10', '3000.00');INSERT INTO
ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE,
COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD005', '01.01.2010', 'S003', 'Oath of The
Vayuputras', '350.00', '10', '3500.00');INSERT INTO ORDERED_BOOK_DETAILS
(ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD,
TOTAL_AMOUNT) VALUES ('ORD006', '01.01.2010', 'S003', 'The Magic of Thinking
Big', '400.00', '10', '4000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID,
ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES
('ORD007', '02.01.2010', 'S001', 'The Immortals of Meluha', '250.00', '155',
'38750.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE,
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007',
'02.01.2010', 'S001', 'The Secret of The Nagas', '300.00', '150',
'45000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE,
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007',
'02.01.2010', 'S001', 'Oath of The Vayuputras', '350.00', '150',
'52150.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE,
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD008',
'02.01.2010', 'S002', 'The Magic of Thinking Big', '400.00', '100',
'40000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE,
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD009',
'02.01.2010', 'S003', 'The Immortals of Meluha', '250.00', '100',
'25000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE,
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0010',
'02.01.2010', 'S003', 'The Secret of The Nagas', '300.00', '10',
'3000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID,
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0011',
'02.01.2010', 'S003', 'Oath of The Vayuputras', '350.00', '10',
'3500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID,
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0012',
'02.01.2010', 'S003', 'The Magic of Thinking Big', '400.00', '10', '4000.00');
SQL:
Get Order_ID, Order_Date, Salesman_ID for different Salesmen :1. Who sold
maximim Copy for a day2. Who sold maximum amount of book for a day3. Who got
maximum order for a day
With Max_Copy (Order_ID, Order_Date, Salesman_ID, Max_Copy_Sold, ToTal_Amount)
As(Select Order_ID, Order_Date, Salesman_ID, Sum(Copy_Sold) As Max_Copy_Sold ,
Sum(ToTal_Amount) From Ordered_Book_Details Group By Order_ID, Order_Date,
Salesman_ID Order By Max_Copy_Sold Desc),
Max_ToTal (Order_ID, Order_Date, Salesman_ID, Copy_Sold, Max_ToTal_Amount)
As(Select Order_ID, Order_Date, Salesman_ID, Sum(Copy_Sold), Sum(ToTal_Amount)
As Max_ToTal_Amount From Ordered_Book_Details Group By Order_ID, Order_Date,
Salesman_ID Order By Max_ToTal_Amount Desc),
Max_Orders (Order_ID, Order_Date, Salesman_ID, Max_Orders_Count) As(Select
List(Order_ID), Order_Date, Salesman_ID, Count(Salesman_ID) As
Max_Orders_Count From Ordered_Book_Details Group By Salesman_ID, Order_Date
Order By Max_Orders_Count Desc)
Select First 1 Max_Copy.Order_ID, Max_Copy.Order_Date, Max_Copy.Salesman_ID,
Max_Copy.Max_Copy_Sold, Max_Copy.ToTal_Amount, Max_ToTal.Order_ID,
Max_ToTal.Order_Date, Max_ToTal.Salesman_ID, Max_ToTal.Copy_Sold,
Max_ToTal.Max_ToTal_Amount, Max_Orders.Order_ID, Max_Orders.Order_Date,
Max_Orders.Salesman_ID, Max_Orders.Max_Orders_CountFrom Max_Copy,
Max_ToTal, Max_Orders
----------
Get Order_ID, Order_Date, Salesman_ID for different Salesmen :
1. Who sold maximim Copy for a day
2. Who sold maximum amount of book for a day
3. Who got maximum order for a day
With Max_Copy (Order_ID, Order_Date, Salesman_ID, Max_Copy_Sold, ToTal_Amount)
As
(Select Order_ID, Order_Date, Salesman_ID, Sum(Copy_Sold) As Max_Copy_Sold ,
Sum(ToTal_Amount)
From Ordered_Book_Details
Group By Order_ID, Order_Date, Salesman_ID Order By Max_Copy_Sold Desc
),
Max_ToTal (Order_ID, Order_Date, Salesman_ID, Copy_Sold, Max_ToTal_Amount) As
(Select Order_ID, Order_Date, Salesman_ID, Sum(Copy_Sold), Sum(ToTal_Amount) As
Max_ToTal_Amount
From Ordered_Book_Details
Group By Order_ID, Order_Date, Salesman_ID Order By Max_ToTal_Amount Desc
),
Max_Orders (Order_ID, Order_Date, Salesman_ID, Max_Orders_Count) As
(Select List(Order_ID), Order_Date, Salesman_ID, Count(Salesman_ID) As
Max_Orders_Count
From Ordered_Book_Details
Group By Salesman_ID, Order_Date Order By Max_Orders_Count Desc
)
Select First 1 Max_Copy.Order_ID, Max_Copy.Order_Date, Max_Copy.Salesman_ID,
Max_Copy.Max_Copy_Sold, Max_Copy.ToTal_Amount,
Max_ToTal.Order_ID, Max_ToTal.Order_Date, Max_ToTal.Salesman_ID,
Max_ToTal.Copy_Sold, Max_ToTal.Max_ToTal_Amount,
Max_Orders.Order_ID, Max_Orders.Order_Date, Max_Orders.Salesman_ID,
Max_Orders.Max_Orders_Count
>From
Max_Copy, Max_ToTal, Max_Orders
----------
CREATE TABLE ORDERED_BOOK_DETAILS
(
ORDER_ID Varchar(50) NOT NULL,
ORDER_DATE Date NOT NULL,
SALESMAN_ID Varchar(10) NOT NULL,
BOOK_NAME Varchar(50) NOT NULL,
BOOK_PRICE Numeric(15,2) DEFAULT 0,
COPY_SOLD Integer DEFAULT 0,
TOTAL_AMOUNT Numeric(15,2) DEFAULT 0,
PRIMARY KEY (ORDER_ID,ORDER_DATE,SALESMAN_ID,BOOK_NAME)
);
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001',
'The Immortals of Meluha', '250.00', '50', '12500.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001',
'The Secret of The Nagas', '300.00', '50', '15000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001',
'Oath of The Vayuputras', '350.00', '50', '17500.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD002', '01.01.2010', 'S002',
'The Magic of Thinking Big', '400.00', '400', '160000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD003', '01.01.2010', 'S003',
'The Immortals of Meluha', '250.00', '10', '2500.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD004', '01.01.2010', 'S003',
'The Secret of The Nagas', '300.00', '10', '3000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD005', '01.01.2010', 'S003',
'Oath of The Vayuputras', '350.00', '10', '3500.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD006', '01.01.2010', 'S003',
'The Magic of Thinking Big', '400.00', '10', '4000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007', '02.01.2010', 'S001',
'The Immortals of Meluha', '250.00', '155', '38750.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007', '02.01.2010', 'S001',
'The Secret of The Nagas', '300.00', '150', '45000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007', '02.01.2010', 'S001',
'Oath of The Vayuputras', '350.00', '150', '52150.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD008', '02.01.2010', 'S002',
'The Magic of Thinking Big', '400.00', '100', '40000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD009', '02.01.2010', 'S003',
'The Immortals of Meluha', '250.00', '100', '25000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0010', '02.01.2010', 'S003',
'The Secret of The Nagas', '300.00', '10', '3000.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0011', '02.01.2010', 'S003',
'Oath of The Vayuputras', '350.00', '10', '3500.00');
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME,
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0012', '02.01.2010', 'S003',
'The Magic of Thinking Big', '400.00', '10', '4000.00');
[Non-text portions of this message have been removed]
