0

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
==============================================

Edited by k_manimuthu: formatting

3
Contributors
20
Replies
50
Views
2 Years
Discussion Span
Last Post by k_manimuthu
Featured Replies
  • 1

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

  • 1

    > 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. Read More

  • 1

    > 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, … Read More

0

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
0

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

Edited by k_manimuthu

1

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

Votes + Comments
+rep to help restore your crystal ball
0

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'

Edited by k_manimuthu

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

WHERE goes before the GROUP BY.

0

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' ?

Edited by k_manimuthu

0

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.

0

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

Edited by k_manimuthu

0

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

0

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 
1

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.

Votes + Comments
thanks for your continuous support
0

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

Edited by k_manimuthu

Attachments dani.png 3.72 KB
0

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 
1

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.

Edited by Shark_1

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.