2

Is there a simple way to achieve this query result to improve its performance. I prefer not to join two tables for every select.

select Product, LCTL, LQ, LCTQL from
(
(select 'Phone / HSI' as Product, (select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='m ECOMM ORD') as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD')) as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD' or c.category_long_name='m ECOMM ORD')) as LCTQL from dual
union all

select '(Phone / HSI) + Prism' as Product,(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='m ECOMM PRISM') as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='CRIS ECOMM PRISM') as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM PRISM' or c.category_long_name='CRIS ECOMM PRISM')) as LCTQL from dual

union all
select '(Phone / HSI) + DTV' as Product,(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='m ECOMM DTV') as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='CRIS ECOMM DTV') as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM DTV' or c.category_long_name='CRIS ECOMM DTV')) as LCTQL from dual

union all
select 'Total' as Product,(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM DTV' or c.category_long_name='m ECOMM PRISM' or c.category_long_name='m ECOMM DTV')) as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='CRIS ECOMM DTV' or c.category_long_name='CRIS ECOMM PRISM' or c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD')) as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM DTV' or c.category_long_name='m ECOMM PRISM' or c.category_long_name='m ECOMM DTV' or c.category_long_name='CRIS ECOMM DTV' or c.category_long_name='CRIS ECOMM PRISM' or c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD' )) as LCTQL from dual)
)

Output Should be as below:

Product                 LCTL    LQ      LCTQL
Phone / HSI         17209   39849   57058
(Phone / HSI) + Prism   4095    6   4101
(Phone / HSI) + DTV 6192    1   6193


Total   10287   39856   50143
Improve this question

1 Answer 1

Reset to default
1

I think your query could be written this way:

SELECT
  product,
  SUM(CASE WHEN c.category_long_name  = 'm ECOMM ORD'      THEN 1 ELSE 0 END) AS LCTL,
  SUM(CASE WHEN c.category_long_name  = 'CRIS ECOMM ORD' 
             OR c.category_long_name  = 'CRIS ECOMM HELD'  THEN 1 ELSE 0 END) AS LQ,
  SUM(CASE WHEN c.category_long_name IN('CRIS ECOMM ORD',
                                        'CRIS ECOMM HELD',
                                        'm ECOMM ORD')     THEN 1 ELSE 0 END) AS LCTQ,
  SUM(CASE WHEN c.category_long_name = 'm ECOMM PRISM'     THEN 1 ELSE 0 END) AS lccc,
  ...
  ...
  COUNT(*) AS "Total"
FROM tasks t
INNER JOIN task_categories c ON t.category_id=c.category_id
WHERE to_date(to_char(t.user_create_date, 'DD-MON-YYYY')) >= ?1
  and to_date(to_char(t.user_create_date, 'DD-MON-YYYY')) <= ?2
GROUP BY product;

Using the CASE expression, with GROUP BY product, you can do this in one query. This is the basic idea.

Also, you might need to use the pivot operator in Oracle, but it is not clear if it would be possible to be written using the pivot operator or not.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.