Apply distinct on one column and order by on another with count

Question

In MySql DB I have

 CustomerId  productId  modelNumber
  CAnWgsN0    C9407R       300     
  CAnWgsN0    C5861W       300       
  CAnWgsN0    C9407R       400       
  CAnWgsN0    C5861W       600       
  CAnWgsN0    C9407R       300       
  KAuK9nB     K3150Z       100       
  KAuK9nB6    K3150Z       100       

and I want my output as

customerId   productId   modelNumber  Count
 CAnWgsN0     C9407R        300         2
 CAnWgsN0     C9407R        400         1
 CAnWgsN0     C5861W        300         1
 CAnWgsN0     C5861W        600         1
 KAuK9nB6     K3150Z        100         2

I am trying with this query but not getting desired result. How can I apply distinct on modelNumber and then group by productId.

SELECT  *
FROM    (SELECT customerId, productId, COUNT(modelNumber) As count, 
                ROW_NUMBER() OVER (PARTITION BY modelNumber ORDER BY productId) AS RowNumber
         FROM   customers) AS a
WHERE   a.RowNumber <=100 ;

It is returning only one row.I have tried this query as well but no luck

SELECT productId, customerId, modelNumber, COUNT(modelNumber) AS count
FROM customers
WHERE modelNumber IN (SELECT DISTINCT modelNumber from customer ORDER BY productId);

Answer 1

Unless I am missing something, you just want to GROUP BY customerId, productId, modelNumber

WITH customers AS (
  (SELECT 'CAnWgsN0' AS customerId, 'C9407R' AS productId, 300 AS modelNumber ) UNION ALL
  (SELECT 'CAnWgsN0', 'C5861W', 300) UNION ALL
  (SELECT 'CAnWgsN0', 'C9407R', 400) UNION ALL
  (SELECT 'CAnWgsN0', 'C5861W', 600) UNION ALL
  (SELECT 'CAnWgsN0', 'C9407R', 300) UNION ALL
  (SELECT 'KAuK9nB6', 'K3150Z', 100) UNION ALL
  (SELECT 'KAuK9nB6', 'K3150Z', 100))

SELECT customerId, productId, modelNumber, COUNT(*) AS count
FROM customers
GROUP BY customerId, productId, modelNumber;