How to combine these two sqls into one?

Question

I have to find out count of records which were created before 2 hours and which were created in last two hours. I can find that using

[ created in last two hours ]

select count(*)
 from trans_calc 
where
I_TRACKING_CODE = 112
and d_entered > (current_timestamp - 2 hours) 

[ created prior to two hours ]

select count(*)
 from trans_calc 
where
I_TRACKING_CODE = 112
and d_entered < (current_timestamp - 2 hours) 

Can we merge these two sqls or infact can we write a single sql which can give us the desired output?

Answer 1

try this:

SELECT
    SUM(CASE WHEN d_entered>(current_timestamp-2 hours) THEN 1 ELSE 0 END AS NewerCount
    ,SUM(CASE WHEN d_entered<=(current_timestamp-2 hours) THEN 1 ELSE 0 END AS OlderCount
    FROM trans_calc 
    WHERE I_TRACKING_CODE = 112

Answer 2

How about

SELECT CASE WHEN (d_entered > (current_timestamp - 2 hrs)
            THEN 'New'
            ELSE 'Old' AS AGE
     , Count(*) as Rows
from trans_calc 
where I_TRACKING_CODE = 112
GROUP BY CASE WHEN (d_entered > (current_timestamp - 2 hrs) THEN 'New' ELSE 'Old'

Answer 3

SELECT SUM(lasttwo) AS lasttwo, COUNT(*) - SUM(lasttwo) AS others
FROM
(
  SELECT 
      CASE WHEN d_entered > (current_timestamp - 2 hours) THEN 1 ELSE 0 END AS lasttwo 
  FROM 
      trans_calc 
  WHERE
      I_TRACKING_CODE = 112
) AS det

Answer 4

Yes:

in one output row,

   select 
       Sum(Case When d_entered > (current_timestamp - 2 hours) 
            Then 1 Else 0 End) afterCount,
       Sum(Case When d_entered <= (current_timestamp - 2 hours) 
            Then 1 Else 0 End) beforeCount
   from trans_calc  
   where I_TRACKING_CODE = 112 

or in two separate rows as BradC suggested above...

Answer 5

SELECT
    d_entered > (current_timestamp - 2 hours) AS IsRecent,
    COUNT(*)
FROM trans_calc
WHERE I_TRACKING_CODE = 112
GROUP BY IsRecent