1

I have a table containing the monthly sales of a car-salesman who sells 3 different brands. I want to create a query which returns a table of the year, month and best selling car brand with the amount sold for that month.

A portion of the table is bellow:

+-----+--------+-----------+-------------+
|NYEAR| NMONTH | CAR_BRAND | AMOUNT_SOLD |
+-----+--------+-----------+-------------+
|2018    12     HONDA            15      |
|2018    12     TOYOTA           12      |
|2018    12     FORD             22      |
|2019     1     HONDA            14      |
|2019     1     TOYOTA           13      |
|2019     1     FORD             34      |
|2019     2     TOYOTA           10      |
|2019     2     FORD             19      |
|2019     2     HONDA            13      |
|2019     3     FORD             14      |
|2019     3     OYOTA            13      |
|2019     3     HONDA            15      |
|2019     4     HONDA            12      |
|2019     4     TOYOTA           14      |
|2019     4     FORD             11      |
|2019     5     FORD             11      |
|2019     5     HONDA            13      |
|2019     5     TOYOTA           10      |
+-----+--------+-----------+-------------+

I am able to get the answer of the Max AMOUNT_SOLD per month but can't get the stores name. Here's the query I tried.

SELECT NYEAR, NMONTH, MAX(SALES_AMOUNT)
FROM MONTHLY_CAR_SALES
GROUP BY NYEAR, NMONTH;

However when I tried to add CAR_BRAND into the select of the query:

SELECT NYEAR, NMONTH, CAR_BRAND, MAX(SALES_AMOUNT)
FROM MONTHLY_CAR_SALES
GROUP BY NYEAR, NMONTH;

I get:

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'USED_CAR_SALES.MONTHLY_CAR_SALES.CAR_BRAND' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

When I try to add CAR_BRAND into the grouping clause it returns the exact same table as above, which shows every brand for every month.

The table I'm after is like this:

+-----+--------+-----------+-------------+
|NYEAR| NMONTH | CAR_BRAND | AMOUNT_SOLD |
+-----+--------+-----------+-------------+
|2018    12     FORD             22      |
|2019     1     FORD             34      |
|2019     2     FORD             19      |
|2019     3     HONDA            15      |
|2019     4     TOYOTA           14      |
|2019     5     HONDA            13      |
+-----+--------+-----------+-------------+
GMB
  • 216,147
  • 25
  • 84
  • 135
RickPeck
  • 165
  • 2
  • 14

1 Answers1

2

You want to pull out the top-selling record per month.

There are several possible approaches (notably, not exists and window function row_number()), but using a correlated subquery usually offers the better performance (and, in my opinion, readability):

select nyear, nmonth, car_brand, sales_amount
from monthly_car_sales cs
where sales_amount = (
    select max(sales_amount) 
    from monthly_car_sales cs1 
    where cs1.nyear = cs.nyear and cs1.nmonth = cs.nmonth
)

For performance, consider an index on (nyear, nmonth, sales_amount).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • self left join with a shifting filter to find a max per group -> `.. FROM monthly_car_sales cs LEFT JOIN monthly_car_sales cs1 ON cs1.nyear = cs.nyear and cs1.nmonth = cs.nmonth # GROUP BY nyear,nmonth AND cs.sales_amount < cs1.sales_amount # MAX(sales_amount ) WHERE cs1.sales_amount IS NULL` i generally believes gives better performance over corelation but it might be tricky to use filtering wise also it is a bit less readable.. – Raymond Nijland Oct 20 '19 at 12:52
  • @GMB That's exactly what I was trying to due, thank you! – RickPeck Oct 20 '19 at 13:13
  • 1
    @RaymondNijland: yes I agree that’s an acceptable option too Mr NijlanD :) – GMB Oct 20 '19 at 14:50