SQL Query Help to get count

Question

Hi:

Need help with a SQL Query:

I have Summary data with ID, Name and then Details data. I would like to display

% of total UID exists in @tblUIDDetails tables (In the above example only 3 UID exists)
% of individual UID loaded in @tblUIDDetails table (Load Flag OK).
% of individual UID loaded in @tblUIDDetails table (Load Flag KO).

DECLARE @tblUIDSummary TABLE  
(UID VARCHAR(10) PRIMARY KEY,  
 UName VARCHAR(20))  
  
INSERT INTO @tblUIDSummary VALUES ('101','TEST1')  
INSERT INTO @tblUIDSummary VALUES ('102','TEST2')  
INSERT INTO @tblUIDSummary VALUES ('103','TEST3')  
INSERT INTO @tblUIDSummary VALUES ('104','TEST4')  
INSERT INTO @tblUIDSummary VALUES ('105','TEST5')  
  
DECLARE @tblUIDDetails TABLE  
(UID VARCHAR(10),  
 LoadFlag VARCHAR(5),  
 Details VARCHAR(50))  
  
INSERT INTO @tblUIDDetails VALUES ('101','OK','XYZ')  
INSERT INTO @tblUIDDetails VALUES ('101','OK','PQR')  
INSERT INTO @tblUIDDetails VALUES ('101','OK','ABCDEF')  
INSERT INTO @tblUIDDetails VALUES ('101','OK','TST')  
INSERT INTO @tblUIDDetails VALUES ('101','OK','TOMDD')  
  
INSERT INTO @tblUIDDetails VALUES ('103','OK','ABC')  
INSERT INTO @tblUIDDetails VALUES ('103','OK','TST2345')  
INSERT INTO @tblUIDDetails VALUES ('103','OK','ZZZ')  
INSERT INTO @tblUIDDetails VALUES ('103','KO','TTT')  
INSERT INTO @tblUIDDetails VALUES ('103','KO','ZOOM')  
  
INSERT INTO @tblUIDDetails VALUES ('105','OK','PPP')  
INSERT INTO @tblUIDDetails VALUES ('105','OK','QQQQQQ')  
INSERT INTO @tblUIDDetails VALUES ('105','OK','DDDD')  
INSERT INTO @tblUIDDetails VALUES ('105','KO','YYYYYYY')  
  
  
     
---- OUTPUT  
SELECT   
    US.UID, US.UName,  
    CASE WHEN COUNT(UD.UID) > 0 THEN 'YES' ELSE 'NO' END AS [UID Loaded],  
    CASE WHEN COUNT(UD.UID) = 0 THEN 0  
         ELSE CAST(SUM(CASE WHEN UD.LoadFlag = 'OK' THEN 1 ELSE 0 END) * 100.0 /   
              COUNT(UD.LoadFlag) AS tinyint) END AS [OK Percentage],  
    CASE WHEN COUNT(UD.UID) = 0 THEN 0  
         ELSE CAST(SUM(CASE WHEN UD.LoadFlag = 'KO' THEN 1 ELSE 0 END) * 100.0 /   
              COUNT(UD.LoadFlag) AS tinyint) END AS [KO Percentage]  
FROM @tblUIDSummary US  
LEFT OUTER JOIN @tblUIDDetails UD ON UD.UID = US.UID  
GROUP BY US.UID, US.UName  
ORDER BY US.UID, US.UName  

I get the output using this query, but I also need the number of records displayed (count) for OK and KO

Thanks!

Answer 1

Try these columns:

count(case UD.LoadFlag when 'OK' then 0 end) as [OK Count],
count(case UD.LoadFlag when 'KO' then 0 end) as [KO Count],