Hi,
Is there any possibilities to use multiple case with multiple group by clause in a single query. I have formed multiple query based on condition as below
SELECT COUNT(1), searchhit
FROM (SELECT (CASE
WHEN category_id = 2 AND sources = '2' THEN
sub_category
END) searchhit
FROM lf_flight_req a)
GROUP BY searchhit
/
SELECT COUNT(1), success
FROM (SELECT (CASE
WHEN category_id = 2 AND sources = '1' AND status = 1 THEN
sub_category
END) success
FROM lf_flight_req a)
GROUP BY success
/
SELECT COUNT(1), booking_success
FROM (SELECT (CASE
WHEN category_id = 2 AND sources = '1' AND
booking_status = 1 THEN
sub_category
END) booking_success
FROM lf_flight_req a)
GROUP BY booking_success
/
create table LF_FLIGHT_REQ
(
REQUEST_ID NUMBER(10),
SOURCES VARCHAR2(10),
CATEGORY_ID NUMBER(10),
SUB_CATEGORY VARCHAR2(100),
STATUS NUMBER(1),
BOOKING_STATUS NUMBER(1)
)
/
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487681, '2', 2, 'CT121', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487682, '2', 2, 'CT122', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487683, '2', 2, 'CT121', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487684, '1', 2, 'CT122', 1, 2);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487685, '2', 2, 'CT122', 1, 2);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487686, '2', 2, 'CT122', 2, 2);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487687, '1', 2, 'CT122', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487688, '2', 2, 'CT121', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487722, '2', 2, 'CT121', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487723, '1', 2, 'CT122', 2, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487724, '2', 2, 'CT122', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487769, '1', 2, 'CT122', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487770, '2', 2, 'CT121', 2, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487771, '2', 2, 'CT121', 1, 1);
insert into LF_FLIGHT_REQ (REQUEST_ID, SOURCES, CATEGORY_ID, SUB_CATEGORY, STATUS, BOOKING_STATUS)
values (37487772, '2', 2, 'CT121', 1, 1);
commit;
/
Thanks,
Jai