2

I have the following tables:

Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)

When I join these tables using:

select YEAR(Sales.saleDate)
     , Vehicles.model
     , count(Vehicles.model) 'Sold'
     , Vehicles.category
  from Vehicles 
  JOIN vehicleSold
    on Vehicles.vin = vehicleSold.vin
  JOIN Sales 
    on Sales.saleID = vehicleSold.saleID
 group 
    by YEAR(Sales.saleDate)
     , Vehicles.model
     , Vehicles.category;

Result is:

+----------------------+-------------+------+----------------+
| YEAR(Sales.saleDate) | model       | Sold | category       |
+----------------------+-------------+------+----------------+
|                 2020 | Altima      |    1 | car            |
|                 2020 | Flying Spur |    2 | car            |
|                 2020 | Lifan E3    |    3 | Electric Moped |
|                 2020 | Ridgeline   |    2 | truck          |
|                 2020 | Shiver      |    4 | motorbike      |
+----------------------+-------------+------+----------------+

Out of this table I want to get the model that was most sold in a category. So, in this case I only want to return a 2020, Flying Spur, car as the only row in category car because it was the most sold in 2020 in its category. I tried using a subquery is MAX(COUNT(*)) but I guess that is not supported in mysql. If anyone could point out my mistake and has any idea how to do this then that would be big help!

0
0

This is a good example of why you should upgrade your servers regularly - this would be relatively trivial using window functions (see below) - however, it is also possible using version 5.6 of MySQL as follows (all the code below can also be found in this fiddle):

  • I did this using "pure" SQL - for solutions using MySQL variables, see here (quite a good site for queries generally), or do a search for "greatest-n-per-group MySQL 5.6" or similar terms.

  • Just to show how much easier this would be with window functions, take a look here - it's reduced the SQL from 37 lines to 17 lines - the plan (I've used MySQL version 8.0.23 - from 8.0.16, you have EXPLAIN ANALYZE) is way more complex for the 5.6 (no window functions) solution - and the performance using profiling (8 runs - changed order) - 5.6 version takes 50% - 100% longer (sometimes more...)! The SQL is at the bottom of this answer - see the fiddle here!

Solution using SQL - no window functions (fiddle here):

Firstly, I put together the tables as per the question:

CREATE TABLE vehicle 
(
  vin VARCHAR (50) NOT NULL PRIMARY KEY,
  model VARCHAR (30) NOT NULL,
  category VARCHAR (30) NOT NULL
);

and:

CREATE TABLE sale
(
  sale_id INTEGER NOT NULL PRIMARY KEY
  -- nothing useful in sale - maybe rethink design?
);

and:

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)
);

And populate them:

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');

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

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

So, I have done some exploratory analysis queries - just to show the process - can be skipped if you're experienced...

First SQL:

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;

Result:

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

Now, I don't really understand why you have a vehicle_sale and a sale table - there is a complete 1-to-1 correspondance between them. Therefore, one of them is surplus to requirements. It also means that the SQL becomes quite messy because we always have to join the vehicle table to the vehicle_sale table through the sale table - requiring more JOINing and more subqueries - but anyway, it can still be done as follows:

There's a couple more queries in the fiddle - you can ask about them if you need to, but our first requirement is a table (i.e. query result) of models with no. of that model sold as follows:

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;

Result:

category    model   cnt
     car       m2   3
     car       m1   1
    elmo       m6   2
    elmo       m5   1
   truck      m10   3
   truck       m9   1

So, we see that in the car category, we have 3 sales of model m2 and 1 sale of model m1.

BUT, we need the greatest-n-per-group of this where n = 1 and the group = category/model. So, we have to do the following:

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;

Result:

category    mcnt
car            3
elmo           2
truck          3

We now have to join this result back to the table (i.e. query result above) containing the models to achieve our final result:

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;

Result:

category    model   cnt  category   mcnt
     car       m2     3       car      3
    elmo       m6     2      elmo      2
   truck      m10     3     truck      3

Solution using window functions (fiddle here):

SELECT a.category, a.model, a.cnt 
FROM
(
  SELECT 
    v.category, v.model,
    COUNT(v.model) AS cnt,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(v.model) DESC) AS rn
  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
WHERE a.rn = 1
ORDER BY a.category;

Result: idem.

1
  • @chimmy102 Glad Verace was able to help you with this one! I would've updated my answer but too busy with work today and didn't get a chance. Best of luck! – J.D. Apr 8 at 19:48
0

You can use a window function like ROW_NUMBER() to generate a unique ID for each record within a grouping (PARTITION) of each value of category like so:

SELECT SalesDateYear, model, Sold, category
FROM
(
    select YEAR(Sales.saleDate) SalesDateYear
         , Vehicles.model
         , count(Vehicles.model) 'Sold'
         , Vehicles.category
         , ROW_NUMBER() OVER (PARTITION BY Vehicles.category ORDER BY COUNT(Vehicles.model) DESC) SortId
      from Vehicles 
      JOIN vehicleSold
        on Vehicles.vin = vehicleSold.vin
      JOIN Sales 
        on Sales.saleID = vehicleSold.saleID
     group 
        by YEAR(Sales.saleDate)
         , Vehicles.model
         , Vehicles.category
) Results
WHERE SortId = 1

(I normally like to use a CTE instead of subquery for these kinds of things, but I don't think your version of MySQL has CTEs, so the above uses a subquery.)

6
  • Hi thanks! Is there another way without using a window function? Because I haven't learned about window functions yet and would prefer the other way if possible. – chimmy102 Apr 8 at 11:31
  • Also i believe row_number() is not supported in 5.6. – chimmy102 Apr 8 at 11:44
  • 1
    @chimmy102 - just an aside, you can search for "simulate row_number in mysql" - there's lots of stuff out there. I would strongly urge you to upgrade and get to know window functions - they are extremely powerful and flexible and will repay any effort you put into learning them many times over. – Vérace Apr 8 at 11:52
  • You can definitely do this using a JOIN... – Vérace Apr 8 at 11:54
  • Please answer this using joins thank you! @Vérace – chimmy102 Apr 8 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.