Here we show how you can use multiple count() with different conditions in single MySql query.

Below is sample table with data

				
					--
-- Table structure for table `student`
--

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`id`, `name`, `subject`, `address`, `gender`, `class`, `age`) VALUES
(1, 'Sanjay', 'Math', 'Noida', 'Male', '12th', 18),
(2, 'Devendra', 'Physics', 'Noida', 'Male', '10th', 17),
(3, 'Rajendra', 'Chemistry', 'Rampur', 'Male', '12th', 21),
(4, 'Rani', 'Social Science', 'Delhi', 'Female', '9th', 13),
(5, 'Saroj', 'English', 'Rampur', 'Female', '12th', 17),
(6, 'Arvind', 'Hindi', 'Noida', 'Male', '10th', 16),
(7, 'Mahesh', 'Math', 'Rampur', 'Male', '12th', 20),
(8, 'Kuldeep', 'Physics', 'Noida', 'Male', '12th', 19),
(9, 'Amit', 'English', 'Rampur', 'Male', '12th', 18),
(10, 'Navdeep', 'Biology', 'Noida', 'Male', '10th', 15),
(11, 'Vedant', 'Biology', 'Delhi', 'Male', '9th', 14),
(12, 'Mukul', 'English', 'Delhi', 'Male', '12th', 17);

ALTER TABLE `student`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `student`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

Here we count records on count total number of rows and base of address column .

Query

				
					SELECT 
COUNT(*) as total_count,
COUNT(if(address='Noida',1,null)) as Noida_count,
COUNT(if(address='Delhi',1,null)) as Delhi_count, 
COUNT(if(address='Rampur',1,null)) as Rampur_count
FROM student;

Result Showing Below

I hope this article helps you.
Thanks for visiting Inflay.com .