N rows in group query not working
Posted by:
E C
Date: January 11, 2018 12:49PM
This is my table
----------------------------------------------------------
CREATE TABLE financials (
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 financials_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 `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('1', 'Canada', 'Doe', '20', '5', '2010-01-31 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('2', 'USA', 'Tyson', '40', '15', '2010-02-14 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('3', 'France', 'Keaton', '80', '25', '2010-03-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('4', 'France', 'Joe', '180', '45', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('5', 'France', 'Bill', '30', '6', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('6', 'France', 'Emma', '15', '2', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('7', 'France', 'Joe', '60', '36', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('8', 'France', 'lammy', '130', '26', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('9', 'France', 'Louis', '350', '12', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('10', 'France', 'dennis', '100', '200', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('11', 'USA', 'zooey', '70', '16', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('12', 'France', 'Alex', '2', '16', '2010-04-25 12:01:01');
----------------------------------------------------------
----------------------------------------------------------
I want to find the top 2 products in each country where cost is greater than 50% of the revenue.
----------------------------------------------------------
select * from
(
select r.CountryId, r.ProductId, r.SumRevenue, r.cost, r.revenue, r.ratio,
(CASE r.CountryId
when @curCountryId
then @curRow := @curRow + 1
else @curRow := 1 and
@curCountryId := CountryId
end) + 1 as CountryCount
from (select CountryId, ProductId, Revenue, cost, reg_date, sum(Revenue) as SumRevenue, (revenue/cost) as ratio
from financials,
(Select @curCountryId :="", @curRow:=0) var
where ratio < 2
group by CountryId,ProductId
order by CountryId,SumRevenue desc) as r
) as result
where result.CountryCount <= 5
----------------------------------------------------------
----------------------------------------------------------
This is my result.
CountryId ProductId SumRevenue cost revenue ratio CountryCount
----------------------------------------------------------
France dennis 100 200 100 0.5 4
========================================================================
However, it should also be returning
France Joe 60 36 1.66
France Alex 2 16 0.125
--------------------------------------------
Help? sorry for the lack of indentation. I can't figure out how to format my message.