- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to get the count of both present and absent students for a year in MySQL?
For this, you can use IF() along with aggregate function SUM(). Let us first create a table −
mysql> create table DemoTable1617 -> ( -> Attendance varchar(20), -> CurrentYear int -> ); Query OK, 0 rows affected (0.48 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1617 values('Present',2019); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1617 values('Absent',2019); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1617 values('Absent',2017); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1617 values('Present',2019); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1617 values('Present',2018); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1617 values('Present',2019); Query OK, 1 row affected (0.14 sec)
Display all records from the table using select statement
mysql> select * from DemoTable1617;
This will produce the following output −
+------------+-------------+ | Attendance | CurrentYear | +------------+-------------+ | Present | 2019 | | Absent | 2019 | | Absent | 2017 | | Present | 2019 | | Present | 2018 | | Present | 2019 | +------------+-------------+ 6 rows in set (0.00 sec)
Following is the query to get the count of both present and absent students for a year
mysql> select sum(if(Attendance='Present',1,0)) as Present , -> sum(if(Attendance='Absent',1,0)) as Absent -> from DemoTable1617 -> where CurrentYear LIKE '2019%';
This will produce the following output −
+---------+--------+ | Present | Absent | +---------+--------+ | 3 | 1 | +---------+--------+ 1 row in set (0.00 sec)
- Related Articles
- On A Rainy Day Out Of 150 Students In A School 25 Were Absent. Find The Percentage Of Students Absent From The School. What Percentage Of Students Were Present?
- Out of 32 students, 8 are absent. What percent of the students are absent?
- The number of students absent in a class were recorded every day for 120 days and the information is given in the following frequency table:No. of students absent ($x$):01234567No. of days ($f$):141050341542Find the mean number of students absents per day.
- On a rainy day, 94 percent of the students were present in a school. If the number of students absent on that day was 174, find the total strength of the school.
- MySQL query to count frequency of students with the same age?
- Get the record of a specific year out of timestamp in MySQL?
- How to get the count of a specific value in a column with MySQL?
- How to get the count of each distinct value in a column in MySQL?
- Get beginning and end date from a specific year in MySQL
- Total number of students of a school in different years is shown in the following table YearsNumber of students19964001998535200047220026002004623A. Prepare a pictograph of students using one symbol to represent 100 students and answer the following questions:(a) How many symbols represent total number of students in the year 2002?(b) How many symbols represent total number of students for the year 1998?B. Prepare another pictograph of students using any other symbol each representing 50 students. Which pictograph do you find more informative?"
- MySQL query to get the count of values and display the count in a new column ordered in descending order
- Get record count for all tables in MySQL database?
- MySQL query to get the count of distinct records in a column
- In a class of 40 students, ( frac{1}{5} ) of the total number of students like to eat rice only, ( frac{2}{5} ) of the total number of students like to eat chapati only and the remaining students like to eat both. What fraction of the total number of students like to eat both?
- Which is the fastest method to get the total row count for a MySQL Query?

Advertisements