MySQL Forums
Forum List  »  General

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.

Options: ReplyQuote


Subject
Written By
Posted
N rows in group query not working
E C
January 11, 2018 12:49PM
E C
January 11, 2018 02:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.