SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Managers ( ID NUMBER NOT NULL PRIMARY KEY, LOGIN VARCHAR2(60) NOT NULL, NAME VARCHAR2(200), OFFICE NUMBER ); CREATE TABLE Upsale ( ID NUMBER NOT NULL PRIMARY KEY, DT VARCHAR2(60) NOT NULL, Manager_ID VARCHAR2(200), CLIENT_ID NUMBER, CALL_ID NUMBER, FACTOR_ID NUMBER, RESULT BOOLEAN ); INSERT INTO Managers (ID, LOGIN, NAME, OFFICE) VALUES (1, 'hemp', 'Hemp A', 4), (2, 'jool', 'Jolly A', 5), (3, 'hobert', 'Hobert F', 5), (4, 'frankjr', 'Gotty F', 7), (5, 'aleensr', 'Allen D', 1), (6, 'goofy', 'Goofy M', 8), (7, 'duckduck', 'Muddy W', 8); INSERT INTO Upsale (ID, DT, Manager_ID, CLIENT_ID, CALL_ID, FACTOR_ID, RESULT) VALUES (3, '2017-02-01', '7', 123, 3, 2, FALSE), (9, '2017-02-03', '7', 90, 9, 1, TRUE), (5, '2017-02-02', '5', 678, 5, 4, TRUE), (12, '2017-02-04', '5', 734, 12, 2, TRUE), (6, '2017-02-02', '4', 890, 6, 2, FALSE), (2, '2017-02-01', '3', 67, 2, 1, FALSE), (11, '2017-02-04', '3', 678, 11, 5, TRUE), (7, '2017-02-03', '2', 123, 7, 3, TRUE), (8, '2017-02-03', '2', 45, 8, 2, FALSE), (1, '2017-02-01', '1', 45, 1, 1, TRUE), (4, '2017-02-01', '1', 45, 4, 4, FALSE), (10, '2017-02-04', '1', 893, 10, 4, TRUE); SELECT Manager_ID, SUM(RESULT::int) AS Accepted_Upsales, COUNT(*) AS Total_Upsales, (SUM(RESULT::int) * 100.0 / COUNT(*)) AS Conversion_Percentage FROM Upsale WHERE DT BETWEEN '2017-02-01' AND '2017-02-04' GROUP BY Manager_ID;