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');
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 9, 2023 at 3:52 pm
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