CODEWITHSUNDEEP
sqlsql-serverboolean-logic
Alvaro
Alvaro

Reputation: 51

Add flag for row existence in another table with group by

For below query I want to have a flag called isHold that will evaluate to 0 if there is no billNo from the view viewBills exists in onHold table and 1 otherwise

select max(t.id) TrackingID , max(vb.billNo) billNo, cb.id  ,
max(case when vb.[count] > 1 then 1 else 0 end) isMultiple ,
max(case when t.TrackingID = 31 then 1 else 0 end) IsCancelled,
max(case when exists (select 1 from OnHold oh
where oh.billNo = billNo) then 1 else 0 end) IsHold

from viewBills vb
join tracking t on vb.billNo = t.billNo
join customerBills cb on vb.billNo = cb.billNo 
join customerPieces cp on  cb.id = cp.customerBillId
where cb.statusid <> 3
group by cb.id

I got this error when executing

Cannot perform an aggregate function on an expression 
containing an aggregate or a subquery.

It's reasonable but how can achieve that?

Upvotes: 0

Views: 455

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

You can go for LEFT OUTER JOIN and do the aggregation as given below:

select max(t.id) TrackingID , max(vb.billNo) billNo, cb.id  ,
max(case when vb.[count] > 1 then 1 else 0 end) isMultiple ,
max(case when t.TrackingID = 31 then 1 else 0 end) IsCancelled,
max(case when oh.billNo IS NOT NULL then 1 else 0 end) IsHold

from viewBills vb
join tracking t on vb.billNo = t.billNo
join customerBills cb on vb.billNo = cb.billNo 
join customerPieces cp on  cb.id = cp.customerBillId
LEFT OUTER JOIN OnHold oh ON oh.billNo = vb.billNo
where cb.statusid <> 3
group by cb.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use outer apply or a left join to move the logic to the FROM clause:

select max(t.id) as TrackingID , max(vb.billNo) as billNo, cb.id ,
       max(case when vb.[count] > 1 then 1 else 0 end) as isMultiple,
       max(case when t.TrackingID = 31 then 1 else 0 end) as IsCancelled,
       max(case when oh.billNo is not null then 1 else 0 end) as IsHold
from viewBills vb join
     tracking t
     on vb.billNo = t.billNo join
     customerBills cb
     on vb.billNo = cb.billNo  join
     customerPieces cp
     on cb.id = cp.customerBillId outer apply
     (select top (1) oh.*
      from OnHold oh
      where oh.billNo = cb.billNo
     ) oh
where cw.statusid <> 3
group by cb.id;

Upvotes: 1

Related Questions