CODEWITHSUNDEEP
javasql
user2693664
user2693664

Reputation: 53

How to count values in different column of table in sql using java

This is database table named attendance _table and i want to count total no. of absent in all subjects i.e total no. absent in MATHS+CA+CN+SP+OOPS. Please help!!!

Upvotes: 1

Views: 298

Answers (2)

Guillaume
Guillaume

Reputation: 18865

Note that this answer is not a direct answer to the question...

You have a problem with your data modelling. You don't respect normal form, which make simple operations harder to execute. You should split your table and store Students, Courses and Attendance in separate tables. :

+-----------+------+
| StudentID | Name |
+-----------+------+
| 1         | Max  |
| 2         | Luke |
+-----------+------+

+-----------+-------+
| CourseId  | Title |
+-----------+-------+
| 1         | Math  |
| 2         | CA    |
+-----------+-------+

+--------------+-----------+---------+------------+
| attendenceId | StudentId | CourseId| Attendance |
+--------------+-----------+---------+------------+
| 1            | 1         | 1       | present    |
| 2            | 1         | 2       | absent     |
| 3            | 2         | 1       | present    |
| 4            | 2         | 2       | present    |
+--------------+-----------+---------+------------+

The query now is much simpler and does not need to be changed if you add new courses:

select count(*) from attendance where Attendance = 'absent'

If you want the number of absence for a specific user, it again quite easy. Or the number of absence for a specific course, again, easy.

Upvotes: 0

Naved Alam
Naved Alam

Reputation: 827

you can use case to find above sum. Take an idea from this query.

select sum(case when maths = 'ABSENT' then 1 else 0 end) + 
       sum(case when ca = 'ABSENT' then 1 else 0 end) + 
       sum(case when cn = 'ABSENT' then 1 else 0 end) 
from attendance_table;

Upvotes: 3

Related Questions