mysql> select count(*) as total_count,Ĭount(case when product='A' then 1 else null end) as A_count,Ĭount(case when product='B' and amount>200 then 1 else null end) as B_countīonus Read : How to Use COALESCE in MySQL You can also get above result using CASE statement as shown below. Similarly, we calculate count for rows where product=B and amount > 200. Please note, it is important to use null in case IF condition fails else even non-matching rows are counted. Else it is not counted (assigned as NULL). So MySQL, counts only those rows where product is A. Our condition is to match rows where product = A. Let us look at the above query in detail.Ĭount(*) counts all rows in table to give total count.Ĭount(if(product=’A’,1,null)) as A_count – when we use an IF condition inside count function, it will only count rows where condition is true. mysql> select count(*) as total_count,Ĭount(if(product='A',1,null)) as A_count,Ĭount(if(product='B' and amount>200,1,null)) as B_count
Here is the SQL query to accomplish the above. Let us say you want total count, count of product A orders, and count of product B orders with amount > 200 in single query. Mysql> insert into product_sales(id, product, order_date, amount)Īlso read : How to Use CASE statement in MySQL
Let us say you have the following table product_sales(id, product, order_date, amount) mysql> create table product_sales( Here are the steps to get multiple counts with different conditions in single MySQL query. Multiple Counts with Different Conditions in single MySQL query In this article, we will look at how to get multiple counts with multiple conditions in MySQL. You can easily get multiple counts with different conditions or criteria in a single query in MySQL, using IF or CASE statements.
0 kommentar(er)
