MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


5.6.51


CREATE TABLE vehicle 
(
  vin VARCHAR (50) NOT NULL PRIMARY KEY,
  model VARCHAR (30) NOT NULL,
  category VARCHAR (30) NOT NULL
);
CREATE TABLE sale
(
  sale_id INTEGER NOT NULL PRIMARY KEY
  -- nothing useful in sale - maybe rethink design?
);
CREATE TABLE vehicle_sale
(
  vs_vin   VARCHAR (50) NOT NULL,
  vs_sale_id INTEGER NOT NULL,
  vs_price INTEGER NOT NULL,
  
  CONSTRAINT vs_vin_fk  FOREIGN KEY (vs_vin)     REFERENCES vehicle (vin),
  CONSTRAINT vs_s_id_fk FOREIGN KEY (vs_sale_id) REFERENCES sale (sale_id)
);
INSERT INTO vehicle VALUES
('v1',  'm1',  'car'),
('v2',  'm2',  'car'),
('v3',  'm2',  'car'),
('v4',  'm2',  'car'),
('v5',  'm5',  'elmo'),
('v6',  'm6',  'elmo'),
('v7',  'm6',  'elmo'),
('v8',  'm6',  'elmo'),
('v9',  'm9',  'truck'),
('v10', 'm10', 'truck'),
('v11', 'm10', 'truck'),
('v12', 'm10', 'truck'),
('v13', 'm10', 'truck'),
('v14', 'm10', 'truck');



Records: 14  Duplicates: 0  Warnings: 0


INSERT INTO sale
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (12), (13), (14);

Records: 13  Duplicates: 0  Warnings: 0


INSERT INTO vehicle_sale
VALUES
('v1',  1,   100), -- m1
('v2',  2,   200), -- m2
('v3',  3,   300), -- m2
('v4',  4,   400), -- m2

('v5',  5,   500), -- m5
('v6',  6,   600), -- m6
('v7',  7,   700), -- m6

('v9',  9,   800),  -- m9
('v12', 12,  900), -- m10
('v13', 13, 1000), -- m10
('v14', 14, 1100); -- m10

Records: 11  Duplicates: 0  Warnings: 0


SELECT 
  v.vin, v.model, v.category,
  s.sale_id,
  vs.vs_vin, vs.vs_sale_id, vs_price
FROM 
  sale s
INNER JOIN vehicle_sale vs
  ON s.sale_id = vs.vs_sale_id
INNER JOIN vehicle v
  ON vs.vs_vin = v.vin;


vin
model
category
sale_id
vs_vin
vs_sale_id
vs_price


v1
m1
car
1
v1
1
100

v2
m2
car
2
v2
2
200

v3
m2
car
3
v3
3
300

v4
m2
car
4
v4
4
400

v5
m5
elmo
5
v5
5
500

v6
m6
elmo
6
v6
6
600

v7
m6
elmo
7
v7
7
700

v9
m9
truck
9
v9
9
800

v12
m10
truck
12
v12
12
900

v13
m10
truck
13
v13
13
1000

v14
m10
truck
14
v14
14
1100


SELECT 
  v.vin, v.model, v.category,
  vs.vs_vin, vs.vs_sale_id, vs_price
FROM vehicle_sale vs
  INNER JOIN vehicle v
    ON vs.vs_vin = v.vin;


vin
model
category
vs_vin
vs_sale_id
vs_price


v1
m1
car
v1
1
100

v2
m2
car
v2
2
200

v3
m2
car
v3
3
300

v4
m2
car
v4
4
400

v5
m5
elmo
v5
5
500

v6
m6
elmo
v6
6
600

v7
m6
elmo
v7
7
700

v9
m9
truck
v9
9
800

v12
m10
truck
v12
12
900

v13
m10
truck
v13
13
1000

v14
m10
truck
v14
14
1100


SELECT 
  v.model, v.category
  , s.sale_id  -- s.sale_id not strictly required...
FROM vehicle_sale vs
  INNER JOIN vehicle v
    ON vs.vs_vin = v.vin
  INNER JOIN sale s
    ON vs.vs_sale_id = s.sale_id;


model
category
sale_id


m1
car
1

m2
car
2

m2
car
3

m2
car
4

m5
elmo
5

m6
elmo
6

m6
elmo
7

m9
truck
9

m10
truck
12

m10
truck
13

m10
truck
14


SELECT 
  v.category, v.model,
  COUNT(v.model) AS cnt
FROM vehicle_sale vs
  INNER JOIN vehicle v
    ON vs.vs_vin = v.vin
  INNER JOIN sale s
    ON vs.vs_sale_id = s.sale_id
GROUP BY v.category, v.model
ORDER BY v.category, cnt DESC;


category
model
cnt


car
m2
3

car
m1
1

elmo
m6
2

elmo
m5
1

truck
m10
3

truck
m9
1


SELECT category, MAX(cnt) AS mcnt
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
GROUP BY category
ORDER BY category;


category
mcnt


car
3

elmo
2

truck
3


SELECT 
  a.category, a.model, a.cnt, 
  y.category, y.mcnt  -- the y values are not required anymore! Left in for explanation
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt
  FROM vehicle_sale vs
    INNER JOIN vehicle v
      ON vs.vs_vin = v.vin
    INNER JOIN sale s
      ON vs.vs_sale_id = s.sale_id
  GROUP BY v.category, v.model
  ORDER BY v.category, cnt DESC
) AS a
JOIN
(
  SELECT category, MAX(cnt) AS mcnt
  FROM
  (
    SELECT 
      v.category, v.model,
      COUNT(v.model) AS cnt
    FROM vehicle_sale vs
      INNER JOIN vehicle v
        ON vs.vs_vin = v.vin
      INNER JOIN sale s
        ON vs.vs_sale_id = s.sale_id
    GROUP BY v.category, v.model
    ORDER BY v.category, cnt DESC
  ) AS x
  GROUP BY category
  ORDER BY category
) AS y
ON a.category = y.category AND a.cnt = y.mcnt
ORDER BY a.category, a.cnt;


category
model
cnt
category
mcnt


car
m2
3
car
3

elmo
m6
2
elmo
2

truck
m10
3
truck
3
vin	model	category	sale_id	vs_vin	vs_sale_id	vs_price
v1	m1	car	1	v1	1	100
v2	m2	car	2	v2	2	200
v3	m2	car	3	v3	3	300
v4	m2	car	4	v4	4	400
v5	m5	elmo	5	v5	5	500
v6	m6	elmo	6	v6	6	600
v7	m6	elmo	7	v7	7	700
v9	m9	truck	9	v9	9	800
v12	m10	truck	12	v12	12	900
v13	m10	truck	13	v13	13	1000
v14	m10	truck	14	v14	14	1100