CODEWITHSUNDEEP
sqlfirebird
Jose Felipe Blum de Araujo
Jose Felipe Blum de Araujo

Reputation: 321

Merge 3 Firebird queries

I created 3 queries to my Firebird database.

1 - Get all families by date

select 
     distinct oh.FAMILY
from 
     TRACKCODE_SUMMARY ts, 
     ORDER_HEAD oh 
where 
     oh.AMF_NUM = ts.AMF_NUM 
     and ts.DATE_INDUCTION >= '2017-04-06';

2 - Get count of produced by date and family

select 
     count(*) AS Produced 
from 
     trackcode_summary ts, 
     order_head oh 
where 
     ts.AMF_NUM = oh.AMF_NUM 
     and ts.date_induction >= '2017-04-06'
     and oh.FAMILY like 'TULIP15BROAD';

3 - Get count of failed by date and family

select 
    count(*) AS Failed 
from 
    trackcode_summary ts, 
    order_head oh 
where 
    ts.AMF_NUM = oh.AMF_NUM 
    and ts.date_induction >= '2017-04-06' 
    and (ts.emr >= 1 or ts.current_status = 200) 
    and oh.FAMILY like 'TULIP15BROAD';

These 3 queries works perfectly separated. I need merge into one query.

How I can do this?

Upvotes: 3

Views: 202

Answers (1)

Stefan Steiger
Stefan Steiger

Reputation: 82186

How about

SELECT 
     oh.FAMILY

     ,
     SUM
     (
        CASE WHEN oh.FAMILY LIKE 'TULIP15BROAD' THEN 1 ELSE 0 END
     ) AS Produced  

     ,
     SUM
     (
        CASE WHEN (ts.emr >= 1 OR ts.current_status = 200) AND oh.FAMILY LIKE 'TULIP15BROAD' THEN 1 ELSE 0 END
     ) AS Failed  

FROM  
     TRACKCODE_SUMMARY ts, 
     ORDER_HEAD oh 
WHERE  
     oh.AMF_NUM = ts.AMF_NUM 
     AND ts.DATE_INDUCTION >= '2017-04-06';

GROUP BY oh.FAMILY

BTW, instead of LIKE 'TULIP15BROAD' you should probably use = 'TULIP15BROAD'

Also, I think you'd better use the JOIN keywords for better clarity.

FROM TRACKCODE_SUMMARY AS ts

INNER JOIN ORDER_HEAD AS oh 
    ON oh.AMF_NUM = ts.AMF_NUM
    AND ts.DATE_INDUCTION >= '2017-04-06'

Upvotes: 3

Related Questions