none
how to calculate percentage using count from 1 single bit type column RRS feed

  • Question

  • I have a table named attendance that has a column named Pre for marking Present as True and Absent as False.

    i am using same column for marking both absent or present now the issue is i want to calculate percentage

    i can create two virtual columns named TotalClasses and Attended i am also correctly getting values of both by using count.

    but the problem is i want to apply percentage formula on these TotalClasses and Attended virtual columns to a third virtual column named Percentage but i am unable to think of any way to do this


    • Edited by Jazz17 Sunday, June 16, 2019 7:19 PM
    Sunday, June 16, 2019 7:18 PM

Answers

  • Hi Jazz17,

    Would you like this one ?

     
    ;with cte as (
    select distinct s.S_ID,s.Name
      ,(select  count(distinct A_Date) from Attendance 
      where  a.C_ID=C_ID ) as Total_Classes,
      (select count(Pre) from Attendance where a.C_ID = C_ID 
      and a.S_ID = S_ID and Pre = 'True') as Attended 
      from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID 
      left outer join Student s on s.S_ID = sc.S_ID 
      left outer  join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID =2)
      select *,
      cast(cast(Attended*1.00/Total_Classes as decimal(20,2)) as varchar(20)) Percentage 
      from cte 
    

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jazz17 Thursday, June 20, 2019 11:52 AM
    Thursday, June 20, 2019 8:14 AM

All replies

  • There is one than one thing in that query which does not really make sense. Had you included the query as text, I could have copied parts of it, but I can't do that from a screen image.

    You have two subqueries against the Attendance table, but the WHERE conditions in this subquery relate only to the instance of Attendance that appears in the main query. I'm not sure what you are calculating, but it may not be what you are looking for.

    The first subquery returns the number of distict dates in Attendance, no matter which student and course.

    And the second subquery counts the number of rows where Pre = 'True'. The WHERE conditions are likely to be true given the outer query.

    Another thing that looks spooky is that all joins are outer joins. Outer join is something you use when you want to retain all rows on the left side (if a left join), no matter whether are any matching rows on the right side or not. For instance, if you want to list all course, no matter if they have had any students or attendance or not, you would have Course LEFT JOIN. But this does not seem to be the case.

    And if you only want to run this query for a single course, why involve the Course table at all? Seems to me that you only need the Student and Attendance tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 16, 2019 7:46 PM
    Moderator
  • select distinct s.S_ID,s.Name
      ,(select  count(distinct A_Date) from Attendance 
      where  a.C_ID=C_ID ) as Total_Classes,
      (select count(Pre) from Attendance where a.C_ID = C_ID 
      and a.S_ID = S_ID and Pre = 'True') as Attended ,
      (select count((Pre))/count(distinct A_Date) from Attendance where a.S_ID=S_ID and a.C_ID=C_ID) as Percentage
      from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID 
      left outer join Student s on s.S_ID = sc.S_ID 
      left outer  join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID =4

    above is the query

    actually i am using this query in c# code with window form there i am using textboxes for getting value from textbox to course id of WHERE in the query i cannot expalin all of my code working but i have checked it and its providing me desired result only thing i am unable to solve is mentioned in the question i.e calculating percentage from virtual columns.

    and yes you can use other joins too

    Sunday, June 16, 2019 9:41 PM
  • Hi Jazz17,

    Per your description, I am afraid that I am in a mess about your requirement.

     

    Could you  please share us your table structure and some sample data along with your expected result clearly?  Also, please show us your logic according to your data. So that we’ll get a right direction and make some test.

     

    If your data is much big , please share us a similar example.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 17, 2019 6:57 AM
  • i am developing a desktop application on visula studio using c# and window forms i have tables for courses and students and teachers so that 1 student can be enrolled in multiple courses and teacher would be able to take attendance of students who are enrolled in the courses..

    1 teacher can teach multiple courses and 1 student can have multiple courses above query is used when teacher is taking attendance and wants to display only the students in the list who are enrolled in that particular course.

    Monday, June 17, 2019 10:13 AM
  • If you want to reuse columns you compute in a SELECT list, you need to use a CTE, Common Table Expression:

    ; WITH CTE AS (
         SELECT ...
         FROM  ...
    )
    SELECT ...
    FROM CTE

    The CTE is just a logical construction.

    Keep mind that if you divide two integers, you get integer division, so you need to cast to a different data type to get percentages.

    And I still think your query is incorrect.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 17, 2019 9:56 PM
    Moderator
  • and you still didn't answer what  i asked for even when i have provided all data and made you aware of the complete scenario
    Tuesday, June 18, 2019 4:49 AM
  • Hi Jazz17,

     

    Thank you for your reply.

     

    In your original table you have four tables and you provide only three tables . Could you  please share us your script of table structure and some sample data along with your expected result clearly , just like following script ?

     

    And Erland means that , you use many subqueries in your script and you can try CTE in following script.

     

    And as my understanding , when you calculate the percentage , you are using table  Attendance  instead of all four tables. Please share us more information.

    IF OBJECT_ID('Course') IS NOT NULL drop table  Course  
    IF OBJECT_ID('Student') IS NOT NULL drop table  Student 
    IF OBJECT_ID('Attendance') IS NOT NULL drop table  Attendance 
    go
    create table Course
    (C_ID int,
    C_Name varchar(10),
    C_Path varchar(20),
    Fees int )
    insert into Course values 
    (1,'java','C:\Users\zaybi\',15000),
    (2,'C++',NUll,12000),
    (3,'C#',NUll,12000),
    (4,'VP',NUll,17000),
    (5,'Web',NUll,15000),
    (6,'OOAD',NUll,8000),
    (14,'Paks-Std',NUll,5000),
    (15,'Coal',NUll,20000),
    (16,'CCN',NUll,7000)
    
    create table Attendance
    (A_ID int,
    A_Date date,
    S_ID int,
    C_ID int,
    Pre varchar(20) )
    insert into Attendance values 
    (300,'2019-05-14',3,4,'Ture'),
    (301,'2019-05-14',16,4,'Ture'),
    (302,'2019-05-14',90,4,'Ture'),
    (303,'2019-05-14',103,4,'Ture'),
    (304,'2019-05-14',104,4,'Ture'),
    (305,'2019-05-14',112,4,'Ture'),
    (320,'2019-05-15',2,2,'Ture'),
    (306,'2019-05-15',3,4,'Ture'),
    (321,'2019-05-15',14,2,'Ture')
    
    create table Student
    (ID int,
    C_ID int,
    S_ID int )
    insert into Student values 
    (88,1,14),(37,1,90),(74,1,108),(77,1,109),(89,2,2),
    (87,2,14),(78,2,109),(66,3,103),(69,3,104),(72,3,107),(79,3,109)
    
    ;with cte as (
    select  C_ID,count(distinct A_Date) Total_Classes from Attendance group by C_ID
    ) 
    ,cte1 as (
    select C_ID,count(Pre) Attended from Attendance where Pre = 'True' group by C_ID
    )
    select Attended/Total_Classes Percentage from cte a join cte1 b
    on a.C_ID=b.C_ID
    


    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 18, 2019 9:58 AM
  • your query did nothing except deleting my attendance table from my database and i had to create it again and i don't remeber exact constraints and types and every other relation that i put on it....now i don't know if it will work correctly or i have to create it all again
    Tuesday, June 18, 2019 7:35 PM
  • i don't really get what else do you want i have provided the whole scenario all tables involved and here is the 4th table of students record

    i clearly have mentioned that i want to calculate percentage from bit type column named Pre in Attendance table and i have successfully generated two virtual columns named Total_Classes and Attended using sub queries but i am unable to put any logic to find the percentage in the third virtual column using values i get in Total_Classes and Attended columns

    what do you mean by structure of table only thing which matters is Column named Pre(type:-bit) which i have mentioned already and every other thing is quiet clear from the pictures like S_ID and C_ID are foreign keys 

    and i am using above query to display data of only those students who are enrolled in that particular course in StudentCourse table and marking false as absent and True as present with A_Date.

    i can't explain any more


    • Edited by Jazz17 Tuesday, June 18, 2019 7:57 PM
    Tuesday, June 18, 2019 7:56 PM
  • your query did nothing except deleting my attendance table from my database and i had to create it again and i don't remeber exact constraints and types and every other relation that i put on it....now i don't know if it will work correctly or i have to create it all again

    So you do did not review Rachel's script before you ran it? Well, don't blame Rachel for that.

    i clearly have mentioned that i want to calculate percentage from bit type column named Pre in Attendance table and i have successfully generated two virtual columns named Total_Classes and Attended using sub queries but i am unable to put any logic to find the percentage in the third virtual column using values i get in Total_Classes and Attended columns

    what do you mean by structure of table only thing which matters is Column named Pre(type:-bit) which i have mentioned already and every other thing is quiet clear from the pictures like S_ID and C_ID are foreign keys

    You should keep in mind that people who answers questions in these forums do in our free time (OK, that is not really true for Rachel, but well anybody else), and we don't want to spend a whole lot of time on a question which is not well-formed. For instance, I am not going to suggest a solution from a query which clearly seems to be incorrect.

    Generally, the advice when you want help with a question like this is that you post CREATE TABLE statements for your table together with INSERT statements with sample data, and then the desired reuslt from that sample data. The expected result you can post as a screeshot if you like, but when we say CREATE TABLE + INSERT we mean just that: this permits us to copy and paste into a query window to develop a tested solution.

    By the way, in my previous post, I gave an outline for a solution. That is how much effort that I am prepared to put into this fow now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 18, 2019 10:02 PM
    Moderator
  • Hi Jazz17,

     

    Thank you for your reply.

     

    Firstly , I am sorry that my script maked your table be deleted. I'll be more careful next time.

     

    Secondly , according to your data , I try to use your script and it will show an error . Did you give the complete data? If not , maybe you can do a simple example for us (CREATE TABLE ….., INSERT INTO……). Also , please share us your expected result.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 19, 2019 6:18 AM
  • sorry if i sounded rude its just that i was very frustrated when my table got deleted as i didn't create any backup thats also my fault so i am really sorry.

    which error you are getting from my script?errors would be more likely because you didn't make foreign keys and relations in the tables S_ID,C_ID in StudentCourse table is composite primary key.

    A_Date,S_ID,C_ID is composite primary key in Attendance table and S_ID and C_ID are foreign keys from StudentCourse Table.

    you can use the same data provided above

    reason that i am not creating a table from query is that i created table using design view in mssql server maangement.

    i want my data to be displayed like in the very first picture that is S_ID,Name,Total_Classes,Attended and Percentage column to display percentage according to the values in Total_Classes column and Attended Column

    Wednesday, June 19, 2019 8:05 AM
  • you wasted a whole lot of time typing this instead you could have simply proposed a solution for which i am here for.

    secondly my query seems incorrect to you that's your problem as you can clearly see that query executed and is providing the accurate results.

    only thing which i asked for is any way to calculate percentage from the values that are present in virtual columns named Total_Classes and Attended

    i am not into queries and database its just that i am developing a project on visual studio c# and this is one of the modules i am coding for and i don't know much about sql so this is all i can provide.

    why are you so obsessed with sample data?i have provided all the necessary details any 1 can create tables now.table data doesn't matter use what  ever data you want just focus on the query and the result outcome which is desired

    Wednesday, June 19, 2019 8:17 AM
  • Hi Jazz17,

     

    My error has nothing to do with foreign keys. I think it is more related to your real data. If your date is much big , I still advice that you could do a simple data and share us expected result (or expected table )to help us get a right direction and make some test.

     

    Here is my result.

    select *
      from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID 
      left outer join Student s on s.S_ID = sc.S_ID 
      left outer  join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID =4
      /*
      C_ID        C_Name     C_Path               Fees        ID          C_ID        S_ID        S_ID        Name       Password   Picture    Contact    Email      Address    A_ID        A_Date     S_ID        C_ID        Pre
    ----------- ---------- -------------------- ----------- ----------- ----------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------- ----------- --------------------
    4           VP         NULL                 17000       NULL        NULL        NULL        NULL        NULL       NULL       NULL       NULL       NULL       NULL       NULL        NULL       NULL        NULL        NULL
      */
    
     select distinct s.S_ID,s.Name
      ,(select  count(distinct A_Date) from Attendance 
      where  a.C_ID=C_ID ) as Total_Classes,
      (select count(Pre) from Attendance where a.C_ID = C_ID 
      and a.S_ID = S_ID and Pre = 'True') as Attended ,
      (select count((Pre))/count(distinct A_Date) from Attendance where a.S_ID=S_ID and a.C_ID=C_ID) as Percentage
      from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID 
      left outer join Student s on s.S_ID = sc.S_ID 
      left outer  join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID =4
      /*
      Msg 8134, Level 16, State 1, Line 87
    Divide by zero error encountered.
      */
    


    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 19, 2019 9:16 AM
  • yes this error is coming because C_ID=4 has no student enrolled in it so when you calculate total_classes that would be zero and Attended would also be equal to zero and when you try to apply percentage formula to that you'll definitely get that error.

    change C_ID value to something else to which any student or students are enrolled for eg in my table students are enrolled in C_ID=6 then this error wont come

    i will take care of this error through my code so this doesnt matter

    Wednesday, June 19, 2019 12:03 PM
  • i commented out this line which is for calculating percentage because some1 else suggested this is required but it provides wrong calculation of both Total_Classes and Attended column because its not having where clause to count Pre='True' for Attended  so ultimately percentage column is also wrong.

    delete this line from the query and provide some logic in its place that can calculate percentage accurtely

    Wednesday, June 19, 2019 12:19 PM
  • Hi Jazz17,

    Would you like this one ?

     
    ;with cte as (
    select distinct s.S_ID,s.Name
      ,(select  count(distinct A_Date) from Attendance 
      where  a.C_ID=C_ID ) as Total_Classes,
      (select count(Pre) from Attendance where a.C_ID = C_ID 
      and a.S_ID = S_ID and Pre = 'True') as Attended 
      from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID 
      left outer join Student s on s.S_ID = sc.S_ID 
      left outer  join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID =2)
      select *,
      cast(cast(Attended*1.00/Total_Classes as decimal(20,2)) as varchar(20)) Percentage 
      from cte 
    

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jazz17 Thursday, June 20, 2019 11:52 AM
    Thursday, June 20, 2019 8:14 AM
  • thanks @Rachel_Wang you did it.....now only thing left is to deal with divide by zero error....this error will come whenever we try to pass C_ID which to which no student is enrolled.

    i know how to take care of this with coding but is there a way to get rid of this error via same query?adding something to this same query?



    ***Edited:-Took care of divide by zero erro with Nullif()function
    • Edited by Jazz17 Thursday, June 20, 2019 5:08 PM
    Thursday, June 20, 2019 11:56 AM
  • Hi Jazz17,

    Would you like this one ?

     
    ;with cte as (
    select distinct s.S_ID,s.Name
      ,(select  count(distinct A_Date) from Attendance 
      where  a.C_ID=C_ID ) as Total_Classes,
      (select count(Pre) from Attendance where a.C_ID = C_ID 
      and a.S_ID = S_ID and Pre = 'True') as Attended 
      from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID 
      left outer join Student s on s.S_ID = sc.S_ID 
      left outer  join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID =2)
      select *,
      cast(cast(Attended*1.00/Total_Classes as decimal(20,2)) as varchar(20)) Percentage 
      from cte 

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    may i ask why you used two cast functions and why second cast function is converting to varchar?i removed this second cast and query executes fine without any error?
    Thursday, June 20, 2019 4:39 PM
  • Hi Jazz17,

    Thank you for your kind reply.

     

    If you would like to avoid 'divide by zero error', please try to use CASE WHEN. In following script , it will provide you more details.

     

    Also, yes, you are right. At the beginning, I would like you to show you the result as '**.**%', just like '75.00%'.So I add two cast().  However , I am sorry that I forgot it. I do a simple example to make something more clear and please check it.

     
    ;with cte as (
    select distinct s.S_ID,s.Name
    ,(select  count(distinct A_Date) from Attendance 
    where  a.C_ID=C_ID ) as Total_Classes,
    (select count(Pre) from Attendance where a.C_ID = C_ID 
    and a.S_ID = S_ID and Pre = 'True') as Attended 
    from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID 
    left outer join Student s on s.S_ID = sc.S_ID 
    left outer  join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID =2)
    select *,
    case when Total_Classes<>0 then cast(cast(Attended*1.00/Total_Classes as decimal(20,2)) as varchar(20)) 
    else null end as Percentage 
    from cte 
    
    
    IF OBJECT_ID('test') IS NOT NULL drop table  test 
    go
    create table test (
    S_ID int,
    Name varchar(10),
    Total_Classes int,
    Attended int
    )
    insert into test values 
    (1,'A',4,3),
    (2,'B',0,0)
    
    select *,
    case when Total_Classes<>0 then cast(cast(Attended*100.00/Total_Classes as decimal(20,2)) as varchar(20)) +'%'
    else null end as Percentage 
    from test 
    /*
    S_ID        Name       Total_Classes Attended    Percentage
    ----------- ---------- ------------- ----------- ---------------------
    1           A          4             3           75.00%
    2           B          0             0           NULL
    */
    
    select *,
    case when Total_Classes<>0 then cast(Attended*1.00/Total_Classes as decimal(20,2))
    else null end as Percentage 
    from test 
    /*
    S_ID        Name       Total_Classes Attended    Percentage
    ----------- ---------- ------------- ----------- ---------------------------------------
    1           A          4             3           0.75
    2           B          0             0           NULL
    */

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 21, 2019 7:26 AM