I am trying to fetch all those "num" from the given table where the "OUT"

We can have multiple records for a single "num" value. We need to print the output in ascending order of num.

Table :

create table bill
( 
  type varchar(5),
  num varchar(12),
  dur int
);


  insert into bill values
  ('OUT',1818,13),
  ('IN', 1818,10),
  ('OUT',1818,7),
  ('OUT',1817,15),
  ('IN',1817,18),
  ('IN',1819,18),
  ('OUT',1819,40),
  ('IN',1819,18)

This is what I am querying : I am grouping the records on "type" in different sub - queries and fetching the records where "OUT" > "IN".

select a.num
from 
(select num,sum(dur) as D
 from bill  
 where type ='OUT'
 group by num) a ,

(select num,sum(dur) as D
        from bill  
         where type ='IN'
            group by num) b 
        
where a.D > b.D
group by a.num
order by 1

My output:     Expected output:
num             num
1817           1818
1818           1819
1819

Thank you

CodePudding user response：

Use conditional aggregation:

SELECT num
FROM bill
GROUP BY num
HAVING SUM(CASE WHEN type = 'OUT' THEN dur ELSE 0 END) >
       SUM(CASE WHEN type = 'IN' THEN dur ELSE 0 END);

CodePudding user response：

I would use condition aggregate function in a subquery and add where type in ('IN','OUT') which might get better performance if you created an index on type at first column

SELECT num
FROM (
    SELECT num,
           SUM(CASE WHEN type = 'OUT' THEN dur ELSE 0 END) outval, 
           SUM(CASE WHEN type = 'IN' THEN dur ELSE 0 END) inval
    FROM bill  
    WHERE type in ('IN','OUT')
    GROUP BY num
) t1
WHERE outval > inval

sqlfiddle