Hi all,
I am not able to get correct count, if col1 (percentage with respect to product) and col2 have uniq value. Below I post my sample table data and expect output and which i query I tried. Please help me to get in to right direction.
CREATE TABLE mysample (
percentage VARCHAR(100) NOT NULL DEFAULT '',
product VARCHAR(100) NOT NULL DEFAULT '',
location VARCHAR(100) NOT NULL DEFAULT ''
);
insert into mysample values
('100.00', 'A', 'TRZ'),
('100.00', 'A', 'TPJ'),
('100.00', 'A', 'MS'),
('2.18', 'B', 'TPJ'),
('2.18', 'B', 'TRZ'),
('20.00', 'C', 'TRZ'),
('20.00', 'C', 'TPJ'),
('20.00', 'C', 'MS'),
('34.62', 'D', 'TPJ'),
('34.62', 'D', 'TRZ'),
('38.94', 'E', 'TRZ'),
('39.82', 'E', 'TPJ'),
('39.90', 'E', 'TPJ'),
('55.56', 'F', 'TRZ'),
('55.56', 'F', 'TPJ'),
('69.23', 'G', 'TRZ'),
('73.08', 'G', 'TPJ'),
('76.92', 'G', 'TPJ'),
('84.62', 'H', 'TRZ'),
('84.62', 'H', 'TRZ'),
('86.67', 'I', 'TPJ'),
('86.67', 'I', 'TPJ'),
('94.74', 'J', 'TPJ'),
('94.74', 'J', 'TPJ');
-- Expected output
100.00 A 3
2.18 B 2
20.0 C 3
34.62 D 2
55.56 F 2
84.62 H 2
86.67 I 2
94.74 J 2
-- the below query I tried
-- But it list out all the percentage and product
-- I need to get the count of a product having the same percentage
-- For example product 'A' appears 3 times the corresponding percentage '100.00'
-- appears three times i am trying to get the scenario
select percentage,product,count(a.product)
from (
select percentage,product from mysample
)a
where a.percentage=percentage
and a.product=product
group by product,percentage