How to select maximum values on condition from select statement?

Question

I have the following table with multiple entries:

Country	Date	Manufacturer
UK	01-01-2020	VW
UK	01-01-2020	VW
UK	01-02-2020	VW
Denmark	13-01-2020	Audi
........	.......	.......

Each line represents the sale of a car, from a country, from a specific date and the manufacturer of the car sold. I have data from two months (January and February of 2020), for two countries, UK and Denmark. In these months multiple car manufacturers have sold cars (not only VW and Audi as the table shows). I want to obtain for each country and each month the most sold cars for a certain manufacturer and the number of cars sold for that specific manufacturer.

I tried this code:

SELECT
    Country,
    DATENAME(MONTH, Date) AS Month,
    Manufacturer,
    COUNT(*) AS no_of_sales 
FROM
    table
GROUP BY
    Country,
    DATENAME(MONTH, Date),
    Manufacturer
ORDER BY
    no_of_sales DESC

But this way I obtain for each country, each month and for each manufacturer how many cars they sold. I want to obtain only 4 rows in this case:

Country	Date	Manufacturer	no_of_sales
UK	January	VW	13
UK	February	Porsche	15
Denmark	January	VW	10
Denmark	Februray	Audi	12

Basically I am not sure how to filter the select statement that I wrote above. Hope I was clear enough for you to understand.

SSMS is just an IDE-like application that is used with several products such as (but not limited to) SQL Server, Azure SQL Edge and Azure Synapse. SSMS is likely irrelevant to the question here. What product are you actually using for your data engine? Tagging [[tag:SSMS]] for your data engine is like tagging [[tag:visual-studio]] for your programming language; it doesn't actually really tell us anything. — Thom A, Aug 10 '23 at 13:15
I'm not really sure what you're asking here. You give us incomplete sample data, and show expected results but don't tell us what those expected results demonstrate (as we can't infer it as we lack sample data). You have an attempt as well, is that attempt not working? Why isn't it working? Are you getting an error? Unexpected results? Undesired behaviour? Something else? — Thom A, Aug 10 '23 at 13:19
To answer your question in the title, *"How to select maximum values on condition from select statement?"*, have you looked at the [`MAX`](https://learn.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql) function or do you want a [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group)? — Thom A, Aug 10 '23 at 13:21

StoneGiant · Accepted Answer · 2023-08-10T14:46:16.980

The way I read your question is that you are able to count the sales of cars by manufacturer, month, and country. That gives you what you want, but you're only interested in the results from a specific set of manufacturers. I interpret this two ways.

My example uses the following data:

SELECT
        Country
       ,Sale_Date
       ,Manufacturer
  INTO
        #SALES
  FROM  (
           VALUES
           ( 'UK', CAST( '20200101' AS date ), 'VW' )
          ,( 'UK', CAST( '20200101' AS date ), 'VW' )
          ,( 'UK', CAST( '20200102' AS date ), 'VW' )
          ,( 'UK', CAST( '20200103' AS date ), 'Porsche' )
          ,( 'Denmark', CAST( '20200113' AS date ), 'Audi' )
        ) S(Country, Sale_Date, Manufacturer)
;

Method 1: I know which two manufacturers I want to see. This is done by a simple WHERE clause.

 SELECT
         S.Country
        ,DATENAME( MONTH, S.Sale_Date ) AS 'Month'
        ,S.Manufacturer
        ,COUNT(*)                       AS 'NbrOfSales'
   FROM
         #SALES  S
  WHERE
         S.Manufacturer IN ( 'VW', 'Audi' )
GROUP BY 
          S.Country
         ,DATENAME( MONTH, S.Sale_Date )
         ,S.Manufacturer
ORDER BY
          NbrOfSales  DESC
;

Which produces:

+---------+---------+--------------+------------+
| Country |  Month  | Manufacturer | NbrOfSales |
+---------+---------+--------------+------------+
| UK      | January | VW           |          3 |
| Denmark | January | Audi         |          1 |
+---------+---------+--------------+------------+

Method 2: I don't know which manufacturers I want. I only want the one that sold the most cars that month. This method requires that you first group your sales and then number the rows. You can do this in a Common Table Expression (CTE) and then select the first row of each group. The ROW_NUMBER() function is partitioned by country and month name so each manufacturer gets a unique row number. The partitions are ordered descending by sales count so that row number one always has the manufacturer who had the most sales in the partition.

WITH SALES_COUNT AS (
     SELECT
             S.Country
            ,DATENAME( MONTH, S.Sale_Date ) AS 'Month'
            ,S.Manufacturer
            ,COUNT(*)                       AS 'NbrOfSales'
            ,ROW_NUMBER() OVER (PARTITION BY
                                              S.Country
                                             ,DATENAME( MONTH, S.Sale_Date )
                                ORDER BY
                                              COUNT(*)  DESC) AS 'ROW_NBR'
       FROM
             #SALES  S
    GROUP BY 
              S.Country
             ,DATENAME( MONTH, S.Sale_Date )
             ,S.Manufacturer
)
SELECT
        *
  FROM
        SALES_COUNT  SC
 WHERE
        SC.ROW_NBR = 1
;

Which produces:

+---------+---------+--------------+------------+---------+
| Country |  Month  | Manufacturer | NbrOfSales | ROW_NBR |
+---------+---------+--------------+------------+---------+
| Denmark | January | Audi         |          1 |       1 |
| UK      | January | VW           |          3 |       1 |
+---------+---------+--------------+------------+---------+

There may be other ways of looking at your question, but those are the only two that jump out to me.

It was the second method that I was trying to find. Thank you so much for taking the time to leave both solutions! I really appreciate it! — singlequit, Aug 10 '23 at 15:56

How to select maximum values on condition from select statement?

1 Answers1