question

ronbarlow-5250 avatar image
0 Votes"
ronbarlow-5250 asked ronbarlow-5250 commented

Percentages

Hello

I've got a list of Students in tbl1

48767-image.png


and I need a query that returns the following report

48768-image.png

It seemed straight forward when I started doing it, but I'm stuck in the mud with it


Any suggestions much appreciated

Thanks in advance Ron




sql-server-transact-sql
image.png (6.0 KiB)
image.png (2.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ronbarlow-5250 commented

Please try the following.
If you really need the % sign in the output, you can try to uncomment the commented line.

SQL

 -- DDL and sample data population, start
 DECLARE @tbl TABLE 
 (
     ID INT IDENTITY PRIMARY KEY
   , exam VARCHAR(20)
   , result VARCHAR(10)
 );
 INSERT INTO @tbl (exam, result) VALUES
 ('Math', 'Pass'),
 ('Math', 'Pass'),
 ('Math', 'Fail'),
 ('English', 'Pass'),
 ('English', 'Pass'),
 ('English', 'Pass'),
 ('Science', 'Fail'),
 ('Science', 'Fail'),
 ('Science', 'Pass');
 -- DDL and sample data population, end
    
 SELECT exam
      , FORMAT(SUM(IIF(result = 'Pass', 1, 0)) * 100.00 / COUNT(*), '###.##') AS PassRate
 --, FORMAT(SUM(IIF(result = 'Pass', 1, 0)) * 1.00 / COUNT(*), '###.##%') AS PassRate
 FROM @tbl
 GROUP BY exam;

Output

 +---------+----------+
 |  exam   | PassRate |
 +---------+----------+
 | English |      100 |
 | Math    |    66.67 |
 | Science |    33.33 |
 +---------+----------+
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Many thanks All.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Please also check another method:

 CREATE TABLE yourtable 
  (
      ID  INT
    , exam CHAR(10)
    , result CHAR(10)
  );
  INSERT INTO yourtable VALUES
  (1,'Math', 'Pass'),(2,'Math', 'Pass'),
  (3,'Math', 'Fail'),(1,'English', 'Pass'),
  (2,'English', 'Pass'),(3,'English', 'Pass'),
  (1,'Science', 'Fail'),(2,'Science', 'Fail'),(3,'Science', 'Pass')
    
 ;WITH cte 
 AS(SELECT *,CASE WHEN result = 'Pass' THEN 1 ELSE 0 END num FROM yourtable)
    
 SELECT exam,FORMAT(SUM(num)*1.00/COUNT(*),'###.#0%') PassRate FROM cte
 GROUP BY exam

Output:

 exam  PassRate
 English       100.00%
 Math          66.67%
 Science       33.33%


If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo


Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.