CODEWITHSUNDEEP
mysql
Nisan Bahar
Nisan Bahar

Reputation: 73

MySQL - Finding how much duplicates are inside the same table given

Considering I have the following two sets of rows (same type) in a WHERE clause:

A   B
1   1
2   2
3   4

I need to find how many A is in B For example, for the given table above, it would be 66% since 2 out of 3 numbers are in B

Another example:

A   B
1   1
2   2
3   4
    5
    3

Would give 100% since all of the numbers in A are in B

Here is what I tried myself: (Doesn't work on all test cases..)

DROP PROCEDURE IF EXISTS getProductsByDate;
DELIMITER //
CREATE PROCEDURE getProductsByDate (IN d_given date)
BEGIN
SELECT 
    Product,
    COUNT(*) AS 'total Number',
    (SELECT 
            (SELECT COUNT(DISTINCT Part) FROM products WHERE Product=B.Product) - COUNT(*)
        FROM
            products AS b2
        WHERE
            b2.SOP < B.SOP AND b2.Part != B.Part) AS 'New Parts',
             CONCAT(round((SELECT 
            (SELECT COUNT(DISTINCT Part) FROM products WHERE Product=B.Product) - COUNT(*)
        FROM
            products AS b2
        WHERE
            b2.SOP < B.SOP AND b2.Part != B.Part)/count(DISTINCT part)*100, 0), '%') as 'Share New'
FROM
    products AS B
WHERE
    b.SOP < d_given
GROUP BY Product;

END//

DELIMITER ;
CALL getProductsByDate (date("2018-01-01"));

Thanks.

Upvotes: 0

Views: 55

Answers (1)

etsa
etsa

Reputation: 5060

Naming your tables TA and TB respectively you could try something like this (test made on MSSQL and Mysql at moment)

SELECT ROUND(SUM(PERC) ,4)AS PERC_TOT
FROM (
    SELECT DISTINCT TA.ID , 1.00/ (SELECT COUNT(DISTINCT ID) FROM TA) AS PERC
    FROM TA 
    WHERE EXISTS ( SELECT DISTINCT ID FROM TB  WHERE TB.ID=TA.ID)
    ) C;

Output with your first sample data set:

PERC_TOT
0,6667

Output with your second sample data set:

PERC_TOT
1,0000

Update (I made the original for two tables, as I was thinking at solution). This is for one single table (is almost the same than the former query): (I used ID1 for column A and ID2 for column B)

SELECT ROUND(SUM(PERC) ,4)AS PERC_TOT
FROM (
    SELECT DISTINCT TA.ID1 , 1.00/ (SELECT COUNT(DISTINCT ID1) FROM TA) AS PERC
    FROM TA 
    WHERE EXISTS ( SELECT DISTINCT ID2 FROM TA AS TB  WHERE TB.ID2=TA.ID1)
    ) C;

Upvotes: 1

Related Questions