Mysql set default to null if multiple rows dont match

Question

I have a table called invoices. to help make this question simple we will say that there are 4 columns in the invoice.

id                    (PK auto int)
booking_id            (int)
is_business_invoice   (1 or 0 or NULL)
amount                (decimal)

A booking can have multiple invoices.

Im tryng to write a query what will determine if for a given booking id they are all business invoices or personal invoices. Where there are multiple invoices of different is_business_invoice types it will default to null. If there are 3 bookings and all 3 are is_business_invoice then it will return 1.

score 1 · Accepted Answer · answered Jun 06 '12 at 15:31

1

select case when count(is_business_invoice) = sum(is_business_invoice) 
            then 1 
            when sum(is_business_invoice) = 0
            then 0
            else null 
      end as IsBusinessInvoices
from invoices
group by booking_id

answered Jun 06 '12 at 15:31

juergen d

201,996
37
293
362

Mysql set default to null if multiple rows dont match

1 Answers1