Need help with SQL Query - Percentages

• sqldba20

  Ten Centuries

  Points: 1308

  More actions

  January 6, 2022 at 11:02 pm

  Go to Answer

  #3970708

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

  Expected Output:

  

• ScottPletcher

  SSC Guru

  Points: 101101

  More actions

  January 6, 2022 at 11:15 pm

  Answer

  #3970712

   

  SELECT 
      US.UID, 
      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
  ORDER BY US.UID

  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".