CASE<\/a> statement to obtain multiple counts. This is a very efficient way to solve the problem, since it scans the table only once. Here is the query to get separate sums of orders where product is A and product is B.<\/p>\n\n\n\nmysql> SELECT sum(orders) as total,
SUM(CASE WHEN product = 'A' THEN orders ELSE 0 END) AS A_count,
SUM(CASE WHEN product = 'B' THEN orders ELSE 0 END) AS B_count
FROM sales;
+-------+---------+---------+
| total | A_count | B_count |
+-------+---------+---------+
| 270 | 120 | 100 |
+-------+---------+---------+<\/pre>\n\n\n\nIn the above query, we use two sum() functions, one each for product A and product B. In each sum() function, we use a CASE statement. For each row, the first CASE statement(used for 2nd column) results in orders<\/em> column value if the product column value is A for that row, else it results in 0. <\/p>\n\n\n\nThe second CASE statement(used for 3rd column) results in orders<\/em> column value if product column value is B, else it results in 0. The sum() function adds up this value to calculate total sales where product is A and product is B.<\/p>\n\n\n\nIf you want to get a row count instead of sum, then modify the query as shown. Instead of using orders column name in your query, use 1 and 0.<\/p>\n\n\n\n
mysql> SELECT count(*) as total,
SUM(CASE WHEN product = 'A' THEN 1 ELSE 0 END) AS A_count,
SUM(CASE WHEN product = 'B' THEN 1 ELSE 0 END) AS B_count
FROM sales;
+-------+---------+---------+
| total | A_count | B_count |
+-------+---------+---------+
| 5 | 5 | 2 |
+-------+---------+---------+<\/pre>\n\n\n\n