Search code examples
mysqlselectsetdefault

Mysql set default to null if multiple rows dont match

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.

Solution 
  • 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