Post reply

Return 80 % match

  • January 9, 2023 at 3:14 pm

    Go to Answer

    #4135515

    I have data with a column (VenSN) that stores Y, N, Null, (N with serial number ie 'N 23423545').  I want to return all the records for each vendor that has 80% or more of the total records for that vendor containing 'Y' in VenSN column.  How do I do this in a query?

     

    Columns:

    VenSN, VendorName, ID

     

    CREATE TABLE VenInfo(
      VendorName Varchar(50) NOT NULL,
      VenSN VARCHAR(30) NOT NULL,
      ID int IDENTITY(1,1) NOT NULL,
      PRIMARY KEY (ID),
    );

    INSERT INTO VenInfo
        (VenSN, VendorName) 
    VALUES 
        ('Y','ABC'),
        ('Y','ABC'),
        ('Y','ABC'),
        ('Y','ABC'),
        ('Y','ABC'),
        ('Y','ABC'),
        ('Y','ABC'),
        ('Y','ABC'),
        ('N','ABC'),
        ('N','ABC'),
        ('Y','ACME'),
        ('Y','ACME'),
        ('N','ACME'),
        ('N','ACME'),
        ('N 9981743','ACME'),
        ('N','ACME'),
        ('N 88473627','ACME'),
        ('N','ACME'),
        ('N','ACME'),
        ('N','ACME'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('Y','CIGMA'),
        ('N 55563784','CIGMA');

     

     

    • This topic was modified 1 year, 10 months ago by  GrassHopper.
    • This topic was modified 1 year, 10 months ago by  GrassHopper.
    • This topic was modified 1 year, 10 months ago by  GrassHopper.
    • This topic was modified 1 year, 10 months ago by  GrassHopper.

  • January 9, 2023 at 3:25 pm

    Answer

    #4135523 

    SELECT VendorName
    FROM VenInfo
    GROUP BY VendorName
    HAVING SUM(CASE WHEN VenSN = 'Y' THEN 1 ELSE 0 END) * 100.0 / SUM(1) >= 80

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 9, 2023 at 3:52 pm

    #4135540

    worked great!  Thank you!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply