Row count in a multi-table conditions

Question

I would like to calculate the rows in the table with each other condition. I have this in some way, but it does not work.

$sql = "SELECT
 (SELECT COUNT(name) FROM orders) as total,
  UNION ALL
 (SELECT COUNT(name) FROM orders  WHERE status='1') as ok,
  UNION ALL
 (SELECT COUNT(name) FROM orders  WHERE status='2') as ko";

score 0 · Answer 1 · answered Sep 15 '17 at 11:48

Use conditional aggregation:

select count(*) as total, sum(status = 1) as ok, sum(status = 2) as ko
from orders;

Note: This assumes that status is really stored as a number. Don't use single quotes for numeric constants (do use them when you really intend a string).

score 0 · Answer 2 · answered Sep 15 '17 at 12:11

You can count data of more than one tableusing simple query

$sql = "SELECT COUNT(name) AS `total`, (SELECT COUNT(name) FROM orders  WHERE status='1') as `second`, (SELECT COUNT(name) FROM orders  WHERE status='2') as `third` FROM orders";

For more information please check here

Row count in a multi-table conditions

2 Answers2