Sum a SELECT query-CodePudding

I currently am running this select query (shorter than the original):

SELECT 'website' service, COUNT(*) 
FROM website 
UNION 
SELECT 'vps' service, COUNT(*) 
FROM vps 
UNION 
SELECT 'other' service, 
COUNT(*) 
FROM other;

I get the below result: Result

 --------- ---------- 
| service | COUNT(*) |
 --------- ---------- 
| website |        2 |
| vps     |        1 |
| other   |        2 |
 --------- ---------- 
3 rows in set (0.01 sec)

I'd like to add up all the results which are listed in the "COUNT(*)" collum.

Expected Result:

 ---------- 
| COUNT(*) |
 ---------- 
|        5 |
 ---------- 
1 rows in set (0.00 sec)

How can I do this?

Thanks!

CodePudding user response：

Based on the comment you should add SUM in an outer query.

Try:

SELECT sum(t1.nr_count)
FROM (
      SELECT 'website' service, COUNT(*) as nr_count  FROM website 
      UNION 
      SELECT 'vps' service, COUNT(*)  as nr_count FROM vps 
      UNION 
      SELECT 'other' service, COUNT(*) as nr_count FROM other       
    ) as t1;