Get the results if product count is greater than 2

[k_manimuthu]

Hi all,

Below is my sample data. I am trying to get the 'product' name only if the percentage count is varying. Could you please suggest your maximum no of 'Mysql' queries to approach this. Thanks in advance.

Input:

==============================================
percentage  product
==============================================
100.00      A
100.00      A
100.00      A
2.18        B
2.18        B
20.00       C
20.00       C
20.00       C
34.62       D
34.62       D
38.94       E
39.82       E
55.56       F
55.56       F
69.23       G
73.08       G
76.92       G
84.62       H
84.62       H
86.67       I
86.67       I
94.74       J
94.74       J
==============================================

Output:
==============================================
percentage  product
==============================================
38.94       E
39.82       E
69.23       G
73.08       G
76.92       G
==============================================

[pritaeas]

Could you please suggest your maximum no of 'Mysql' queries to approach this

Just one I think:

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

[k_manimuthu]

Hi pritaeas,

it's working fine. Actually my original table contains another more columns also. I want to apply where condition also in the above query. could you pls suggest

[pritaeas]

Show the relevant data, the required output, and what you've tried.

[k_manimuthu]

Hi pritaeas

Please see what i tried

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');


    -- i need to get the results where location is tpj 
    -- the below query i tried
    SELECT *
    FROM mysample
    GROUP BY percentage, product
    HAVING COUNT(*) = 1 where location='TPJ'

[pritaeas]

SELECT *
FROM mysample
WHERE location = 'TPJ'
GROUP BY percentage, product
HAVING COUNT(*) = 1 

WHERE goes before the GROUP BY.

[k_manimuthu]

Hi pritaeas,

The above query list all the percentage. Is this possible to get variying percentage product name only and it's mapped with the location 'TPJ' ?

[pritaeas]

Is this possible to get variying percentage product name only and it's mapped with the location 'TPJ' ?

If you don't explain with an example, I don't know what you mean.

[k_manimuthu]

Input:

-----------------------------
percentage  product location
-----------------------------
100           A       TRZ
100           A       TPJ
100           A       MS
2.18          B       TPJ
2.18          B       TRZ
20            C       TRZ
20            C       TPJ
20            C       MS
34.62         D       TPJ
34.62         D       TRZ
38.94         E       TRZ
39.82         E       TPJ
39.9          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

This input 'product' E and G only having various percentage. Remaing product having the same percentage only.

output1:
This output having the information of variying percentage only from the input details.

38.94   E       TRZ
39.82   E       TPJ
39.9    E       TPJ
69.23   G       TRZ
73.08   G       TPJ
76.92   G       TPJ

output2:
This output having the information of the location code as 'TPJ' from the output1.

39.82   E       TPJ
39.9    E       TPJ
73.08   G       TPJ
76.92   G       TPJ

[pritaeas]

The query I posted earlier should do that, or am I missing something?

[k_manimuthu]

Your earilier query give the results of all the % (uniq) field.
If a query extract the details from variying % and it's related to 'TPJ' then it's fine. Thanks

[pritaeas]

You want to records with a single result removed? I get it now.

[pritaeas]

This might be it:

SELECT *
FROM (
    SELECT product
    FROM mysample
    WHERE location = 'TPJ'
    GROUP BY product
    HAVING COUNT(*) > 1
) tmp, mysample
WHERE tmp.product = mysample.product
GROUP BY tmp.percentage, tmp.product
HAVING COUNT(*) = 1 

[k_manimuthu]

Hi pritaeas,

I didn't got the answer from the above query. But i learn something form your query. Thanks

[pritaeas]

It doesn't work, or what?

[k_manimuthu]

Above query doesn't work for the expected output

[pritaeas]

Above query doesn't work for the expected output

You'll have to tell me what's wrong with it then. I am not clairvoyant.

[k_manimuthu]

The earlier query give the output as the screenshot which i attached here.
But actually i am expecting
1) select the variying percentage from the input data. ( output as I mentioned in my earlier thread. )
2) Apply the condition " where location='TPJ' "

and expecting the output as

39.82   E       TPJ
39.9    E       TPJ
73.08   G       TPJ
76.92   G       TPJ

[pritaeas]

I've forgotten to put location in both places, try this:

SELECT *
FROM (
    SELECT product
    FROM mysample
    WHERE location = 'TPJ'
    GROUP BY product
    HAVING COUNT(*) > 1
) tmp, mysample
WHERE tmp.product = mysample.product
AND mysample.location = 'TPJ'
GROUP BY tmp.percentage, tmp.product
HAVING COUNT(*) = 1 

[Santanu.Das]

GROUP BY tmp.percentage, tmp.product

For this line you can get an error, because percentage column is not included in query.

The Select Statement should be

SELECT tmp.percentage, tmp.product, tmp.location
FROM (
    SELECT *
    FROM mysample
    WHERE location = 'TPJ'
    GROUP BY percentage, product
    HAVING COUNT(*) = 1
) tmp, mysample
WHERE tmp.product = mysample.product
AND mysample.location = 'TPJ'
GROUP BY tmp.percentage, tmp.product
HAVING COUNT(*) > 1;

Hope it can help you.

[k_manimuthu]

Thanks a lot Shark_1,
It's solve my problem.