Using limit in group by

Question

This is my table.

Financials:
    Date
    CountryID
    ProductID
    Revenue
    Cost

I need to find the top 5 products by revenue for each country. Some products will be listed more than once, so I need to sum the revenue for each product.

SELECT
  Financials.CountryID,
  Financials.ProductID,
  SUM(Financials.Revenue) AS total
FROM Financials
INNER JOIN (SELECT
  CountryID,
  GROUP_CONCAT (ProductID ORDER BY Revenue DESC) grouped_ID
FROM Financials
GROUP BY CountryID) group_max
  ON Financials.CountryID = group_max.CountryID
  AND FIND_IN_SET(ProductID, grouped_ID) BETWEEN 1 AND 6

GROUP BY Financials.ProductID
ORDER BY Financials.CountryID, total DESC

This what I have so far. It isn't working. Help?

EDIT

I have been using https://sqltest.net/. please see the insert commmands that I user below

CREATE TABLE mysql_test ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
CountryID VARCHAR(30) NOT NULL, 
ProductID VARCHAR(30) NOT NULL,  
Revenue VARCHAR(50), 
cost VARCHAR(50), 
reg_date TIMESTAMP 
); 

CREATE TABLE mysql_test_sql ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
CountryID VARCHAR(30) NOT NULL, 
ProductID VARCHAR(30) NOT NULL,  
Revenue VARCHAR(50), 
cost VARCHAR(50), 
reg_date TIMESTAMP 
); 

INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('1', 'Canada', 'Doe', '20', '5', '2010-01-31 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('2', 'USA', 'Tyson', '40', '15', '2010-02-14 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('3', 'France', 'Keaton', '80', '25', '2010-03-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('4', 'France', 'Joe', '180', '45', '2010-04-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('5', 'France', 'Bill', '30', '6', '2010-04-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('6', 'France', 'Emma', '15', '2', '2010-04-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('7', 'France', 'Joe', '60', '36', '2010-04-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('8', 'France', 'Jammer', '130', '26', '2010-04-25 12:01:01'); 

INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('9', 'France', 'Louis', '350', '12', '2010-04-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('10', 'France', 'dennis', '100', '175', '2010-04-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('11', 'USA', 'zooey', '70', '16', '2010-04-25 12:01:01'); 
INSERT INTO `mysql_test` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('12', 'France', 'Alex', '2', '16', '2010-04-25 12:01:01');

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jan 11 '18 at 08:27
I assume that the OP uses mysql. Because group concat feature is available for mysql. — lucky, Jan 11 '18 at 08:35
"sql-server-group-concat" tag should be edited. Because it causes get people confused. — lucky, Jan 11 '18 at 08:43
Possible duplicate of [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Valerica, Jan 11 '18 at 09:34

score 0 · Answer 1 · answered Jan 11 '18 at 12:07

Here is one method:

select countryId,
       substring_index(group_concat(productId order by revenue desc), ',', 5) as top5
from (select countryId, productId, sum(revenue) as revenue
      from mysql_test
      group by countryId, productId
     ) cp
group by countryId;

Note: There is a default internal limit of about 1028 characters for the intermediate result for the group_concat(). This should work up to a few hundred products per country. The default length is a system setting and it can be changed.

Using limit in group by

1 Answers1