SHARE
TWEET

Untitled

a guest Jun 20th, 2019 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. C1 : 85
  2. C3 : 70
  3. C2 : 67
  4.      
  5. SELECT
  6. ccode,
  7. ROUND((passed::numeric(15,2) / taken_course::numeric(15,2)) * 100, 0) AS percentage_passed
  8. FROM(
  9.     SELECT
  10.     ccode,
  11.     sum(CASE WHEN grade > 2 THEN 1 ELSE 0 END) AS passed,
  12.     count(1) AS taken_course
  13.     FROM
  14.     hasStudied
  15.     GROUP BY ccode
  16.     ) foo
  17. ORDER BY ccode
  18. -- since you want to order DESC by values, instead do
  19. -- ORDER BY percentage_passed
  20.      
  21. create table #temp(StId int, ccode varchar(5), grade varchar(1))
  22. insert into #temp Values (1,'A1','A'),(1,'A1','F'),(2,'A2','B'),(3,'A2','F'),(4,'A2','F'),(4,'A3','F'),(5,'A3','F')
  23.  
  24. ;with cte as (
  25.     select ccode
  26.     from #temp
  27.     group by ccode
  28. )
  29. select cte.ccode,ratioOfPass = cast(sum(case when t.grade <> 'F' then 1.0 else 0.0 end) as float) / count(*)
  30. from cte
  31. inner join #temp t on t.ccode = cte.ccode
  32. group by cte.ccode
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top