
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How MySQL aggregate functions can be combined with MySQL IF() function?
Combining MySQL aggregate functions with MySQL IF() function can be very helpful to get the specific output we want. Consider the following queries which combine SUM() and COUNT() aggregate functions with IF() function.
Example
mysql> Select SUM(IF(Language = 'English', 1, 0)) As English, SUM(IF(Language <> 'English',1,0)) AS "Non-English" from Students; +---------+-------------+ | English | Non-English | +---------+-------------+ | 5 | 4 | +---------+-------------+ 1 row in set (0.00 sec)
The above query combines SUM() aggregate function with IF() function to get the output of English speaking students and non-English speaking students from ‘Students’ table.
mysql> Select COUNT(IF(country = 'USA', 1, NULL))AS USA, -> COUNT(IF(country = 'UK', 1, NULL))AS UK, -> COUNT(IF(country = 'France', 1, NULL))AS France, -> COUNT(IF(country = 'Russia', 1, NULL))AS Russia, -> COUNT(IF(country = 'Australia', 1, NULL))AS Australia, -> COUNT(IF(country = 'INDIA', 1, NULL))AS INDIA, -> COUNT(IF(country = 'NZ', 1, NULL))AS NZ FROM Students; +-----+----+--------+--------+-----------+-------+----+ | USA | UK | France | Russia | Australia | INDIA | NZ | +-----+----+--------+--------+-----------+-------+----+ | 2 | 1 | 1 | 1 | 1 | 2 | 1 | +-----+----+--------+--------+-----------+-------+----+ 1 row in set (0.07 sec)
The above query combines COUNT() aggregate function with IF() function to get the output of a number of countries from ‘Students’ table.
- Related Articles
- Call aggregate function in sort order with MySQL
- How can MySQL COALESCE() function be used with MySQL SUM() function to customize the output?
- How MySQL LTRIM() and RTRIM()functions can be used with WHERE clause?
- How can CONCAT() function be used with MySQL WHERE clause?
- How wildcard characters can be used with MySQL CONCAT() function?
- How can CONCAT_WS() function be used with MySQL WHERE clause?
- How LOCATE() function can be used with MySQL WHERE clause?
- How can MySQL REPLACE() function be used with WHERE clause?
- How MySQL CONCAT() function, applied to the column/s of a table, can be combined with the column/s of other tables?
- How can MySQL SUBSTRING() function be used with FROM and FOR keywords?
- Get the maximum value of a column with MySQL Aggregate function
- How can we combine functions in MySQL?
- How to add column values in MySQL without using aggregate function?
- How to get MySQL combined field result?
- How Groups function can be used in MySQL SELECT clause?

Advertisements