'How to get multiple counts and sum with one SQL query?

I have 3-5 mysql query:

SELECT COUNT(*) FROM aso_repairs WHERE repairs_date_end = '0000-00-00 00:00:00' AND repairs_supply_repair_users_id = ''
SELECT COUNT(*) FROM aso_repairs WHERE repairs_date_end != '0000-00-00 00:00:00' AND repairs_supply_repair_users_id != '0'
SELECT repairs_id FROM aso_repairs ORDER BY repairs_id DESC

How I can make this in one query?

Like:

 (FIRST COUNT) AS count1, (SECOND COUNT) AS count2, (LAST QUERY) AS count3
mysql


Solution 1:[1]

use union and make every select with 3 columns :

select sum(count1),sum(count2),sum(count3)
from 
(
SELECT COUNT(*) as 'count1',0 as 'count2' ,0 as 'count3' FROM aso_repairs WHERE repairs_date_end = '0000-00-00 00:00:00' AND repairs_supply_repair_users_id = ''
union
SELECT 0 as 'count1',COUNT(*) as 'count2',0 as 'count3' FROM aso_repairs WHERE repairs_date_end != '0000-00-00 00:00:00' AND repairs_supply_repair_users_id != '0'
union
SELECT 0 as 'count1' ,0 as 'count2',repairs_id as 'count3' FROM aso_repairs ORDER BY repairs_id DESC
)RS_count

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Drew

Related Questions