Sum of multiple union select showing every result and total-CodePudding

My SQL shows the count of records in multiple tables

 SELECT 'S_MH_DSC_Abandoned' Table_N ,COUNT(*) Count FROM S_MH_DSC_Abandoned
 UNION SELECT 'S_MH_DSC_ExclAbandoned' Table_N, COUNT(*) Count FROM S_MH_DSC_ExclAbandoned
 UNION SELECT 'S_MH_Private_All' Table_N, COUNT(*) Count FROM S_MH_Private_All

I want to show this plus the total of the values in the last column as above.

I have tried

Select 'Sum Total' Table_N, sum(a) as Total from 
(
 SELECT 'S_MH_DSC_Abandoned',COUNT(*) a FROM S_MH_DSC_Abandoned
 UNION SELECT 'S_MH_DSC_ExclAbandoned', COUNT(*) a FROM S_MH_DSC_ExclAbandoned 
 UNION SELECT 'S_MH_Private_All', COUNT(*) a FROM S_MH_Private_All
 )

But only get the total without the other values.

How can I get both the working counts and the total?

This is using QGIS flavour of SQL - https://sqlite.org/lang.html

CodePudding user response：

I don't use SQLite.

However, reviewing its documentation, it looks as if it doesn't know ROLLUP (which comes handy in such a case):

SQL> with temp (job, sumsal) as
  2    (select 'Clerk', sum(sal) from emp where job = 'CLERK'   union all
  3     select 'Mgr'  , sum(sal) from emp where job = 'MANAGER' union all
  4     select 'Sale' , sum(sal) from emp where job = 'SALESMAN'
  5    )
  6  select job, sum(sumsal) sumsal
  7  from temp
  8  group by rollup (job);

JOB       SUMSAL
----- ----------
Clerk       4150
Mgr         8275
Sale        5600
           18025

SQL>

Therefore, simulate it with a union:

SQL> with temp (job, sumsal) as
  2    (select 'Clerk', sum(sal) from emp where job = 'CLERK'   union all
  3     select 'Mgr'  , sum(sal) from emp where job = 'MANAGER' union all
  4     select 'Sale' , sum(sal) from emp where job = 'SALESMAN'
  5    )
  6  select job, sumsal
  7  from temp
  8  union
  9  select 'Total', sum(sumsal)
 10  from temp;

JOB       SUMSAL
----- ----------
Clerk       4150
Mgr         8275
Sale        5600
Total      18025

SQL>

Using your query:

with temp (tname, cnt) as
  (SELECT 'S_MH_DSC_Abandoned'    , COUNT(*) FROM S_MH_DSC_Abandoned     union all
   SELECT 'S_MH_DSC_ExclAbandoned', COUNT(*) FROM S_MH_DSC_ExclAbandoned union all
   SELECT 'S_MH_Private_All'      , COUNT(*) FROM S_MH_Private_All
  )
select tname, cnt
from temp
union
select 'Total', sum(cnt)
from temp;