Berry Jones Berry Jones -4 years ago 81
MySQL Question

MySQL group by column and set variable if one row contains a specific value

Say I have the following table:

Type |Ownership
-------------------------
Drill | owned
Drill | owned
Plant | owned
Plant | rental
Light Plant | owned
Light Plant | owned
Light Plant | owned
Light Plant | rental


I would like to group these by Type only, and if a single record of a Type contains ownership = "rental" display ownership as "rental

So this is the result I want:

Type |Ownership
-------------------------
Drill | owned
Plant | rental
Light Plant | rental


What I've done:

SELECT Type, Ownership
FROM table
GROUP BY Type, Ownership


Of course this results in getting multiple records of a Type (if this type has two kinds of ownerships)

Answer Source

Demo: http://rextester.com/EAAGP39848

SELECT Type, IF(SUM(Ownership = 'rental'), 'rental', 'owned') AS Ownership2
  FROM type_ownership
  GROUP BY Type;

Inspired from: http://stackoverflow.com/a/30285287/1435132

Edit (explanation):

SELECT *, Ownership = 'rental' AS isRental FROM type_ownership;

isRental is 1 when Ownership = rental

SELECT Type, SUM(Ownership = 'rental') AS sumRental, IF(SUM(Ownership = 'rental'), 'rental', 'owned') AS Ownership2
FROM type_ownership
GROUP BY Type;

Here sumRental is count of records where ownership is rental in that type.

Hence, IF(SUM(Ownership = 'rental'), 'rental', 'owned') returns rental if sum is greater than 0 i.e. any ownership is rental.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download