MySQL - Getting summary of multiple grouped rows ?

Question

2 tables: owners & cars

An owner can have many cars. A car can be marked as usable_offroad, usable_onroad, or both. The cars table has usable offroad and usable_onroad fields which can be set to 0 or 1 (no or yes)

Consider the following query:

SELECT *
FROM owners
LEFT JOIN cars on cars.owner_id = owners.id
GROUP BY owners.id
ORDER BY owners.last_name

My goal is to return a list of owners, and whether or not each owns a an onroad or offroad vehicle, or both:

    Last Name    First Name    Has Offroad Car      Has Onroad Car
----------------------------------------------------------------------
    Smith        Todd          Yes                   No
    Smith        Tom           Yes                   Yes
    Test         Sue           No                    Yes
    Thumb        Joe           No                    No
    White        Al            Yes                   No

How do I query this? Was thinking of using ROLLUP, but would prefer if the summary wasn't an appended row but an actual field on the already grouped owner row instead.

Answer 1

Using a sub-query to sum all the flags, and then checking if one is > 1 should do the job:

SELECT last_name, first_name,
       CASE WHEN usable_offroad_count > 0 THEN 'Yes' ELSE 'No' END has_offroad_car,
       CASE WHEN usable_onroad_count > 0 THEN 'Yes' ELSE 'No' END has_onroad_car
FROM (
    SELECT owners.last_name, owners.first_name,
           SUM( cars.usable_offroad ) usable_offroad_count,
           SUM( cars.usable_onroad ) usable_onroad_count
    FROM owners
    LEFT JOIN cars on cars.owner_id = owners.id
    GROUP BY owners.id
)
ORDER BY last_name

Answer 2

SELECT * ,
if ( cars.usable offroad = 1 and  usable_onroad= 1 , 'Both' 
     , if( cars.usable offroad = 1 and  usable_onroad= 0  , 'Offroad' , 'Onroad')
   )  as Status
FROM owners
LEFT JOIN cars on cars.owner_id = owners.id
GROUP BY owners.id
ORDER BY owners.last_name

Answer 3

Try this:

SELECT
    T1.lastname,
    T1.firstname,
    T1.id in (SELECT owner_id FROM cars WHERE usable_offroad) AS `Has Offroad Car`,
    T1.id in (SELECT owner_id FROM cars WHERE usable_onroad) AS `Has Onroad Car`
FROM owners T1
ORDER BY T1.lastname, T1.firstname

Results:

'Smith', 'Todd', 1, 0
'Smith', 'Tom', 1, 1
'Test', 'Sue', 0, 1
'Thumb', 'Joe', 0, 0
'White', 'Al', 1, 0

Here's my test data:

CREATE TABLE owners (id INT NOT NULL, firstname NVARCHAR(100) NOT NULL, lastname NVARCHAR(100) NOT NULL);
INSERT INTO owners (id, firstname, lastname) VALUES
(1, 'Todd', 'Smith'),
(2, 'Tom', 'Smith'),
(3, 'Sue', 'Test'),
(4, 'Joe', 'Thumb'),
(5, 'Al', 'White');

CREATE TABLE cars (id INT NOT NULL, owner_id INT NOT NULL, usable_onroad INT NOT NULL, usable_offroad INT NOT NULL);
INSERT INTO cars (id, owner_id, usable_offroad, usable_onroad) VALUES
(1, 1, 1, 0),
(2, 2, 1, 0),
(3, 2, 0, 1),
(4, 3, 0, 1),
(5, 3, 0, 1),
(6, 5, 1, 0);

Answer 4

Use:

   SELECT DISTINCT
          o.lastname,
          o.firstname,
          CASE WHEN COALESCE(y.num_offroad, 0) > 0 THEN 'yes' ELSE 'no' END AS "Has Offroad Car"
          CASE WHEN COALESCE(x.num_onroad, 0) > 0 THEN 'yes' ELSE 'no' END AS "Has Onroad Car"
     FROM OWNERS o
LEFT JOIN (SELECT c.owner_id,
                  COUNT(*) AS num_onroad
             FROM CARS c
            WHERE c.usable_onroad = 1
         GROUP BY c.owner_id) x ON x.owner_id = o.id
LEFT JOIN (SELECT c.owner_id,
                  COUNT(*) AS num_offroad
             FROM CARS c
            WHERE c.usable_offroad = 1
         GROUP BY c.owner_id) y ON y.owner_id = o.id