Get count if col1and col2 are same

[k_manimuthu]

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

[pritaeas]

Isn't that just this:

SELECT percentage, product, COUNT(*)
FROM mysample
GROUP BY percentage, product

[k_manimuthu]

It's almost close to my expected output. I want to list out only the 'percentage' and 'product' data having the uniq values with repect to the column. But the above output gives the varying percentage and these products also. I want to eleminate the variying percentage.

[minitauros]

So if there is more than one occurence of the same product with the same percentage, you don't want it listed?

[k_manimuthu]

@minitauros : Yes

From the example in the OP, I assumed that you don't want to list products having different percentages. That's different to what minitauros (apologies if I got it wrong m!) is asking. Confused.

[k_manimuthu]

Actually I want two types of output. I am happy if you are providing these answers.

output 1 : As I mentioned in my initial post ( keep uniq values for product with respect the same percentage value)
output 2 : List out the product with varying percentage.

SELECT product, percentage, COUNT(*) FROM product GROUP BY product, percentage HAVING COUNT(*) > 1

Will give all product-percentages where more than one of them exists, this means you could have more than one entry for each product. For your data, this gives...

product  percentage  COUNT(*)  
-------  ----------  ----------
A        100.00               3
B        2.18                 2
C        20.00                3
D        34.62                2
F        55.56                2
H        84.62                2
I        86.67                2
J        94.74                2

If you just want one entry per product, where each product only has ONE percentage:

SELECT product, percentage, cnt FROM (SELECT product, percentage, COUNT(*) AS cnt FROM product GROUP BY product, percentage) AS tbl GROUP BY product HAVING COUNT(*) = 1

Gives...

product  percentage     cnt  
-------  ----------  --------
A        100.00             3
B        2.18               2
C        20.00              3
D        34.62              2
F        55.56              2
H        84.62              2
I        86.67              2
J        94.74              2

Effectively the same result for your dataset, but results could differ with different data.

[k_manimuthu]

Thanks diafol,

Your queries solve my problem, especially the second query. Thanks a lot