oracle count query with union-CodePudding

I have a query with union all functionality each giving me count(*) return from respective queries and another count query like below. I want an outer query that gives the total.

1st query

select count(*) from a
union all
select count(*) from b;

2nd query

select count(*) from xy;

I want output like this in 2 rows:

xy 20
ab 50

something like above. How can I achieve this in oracle? please suggest the best way to do this.

I wrote a select and union all but not sure how to proceed further.

CodePudding user response：

One option is to sum counts returned by the 1st query and then union it with the 2nd; also, add constants which show the source:

select 'ab' what, (select count(*) from a)   (select count(*) from b) cnt from dual
union all
select 'xy', count(*) from xy;

CodePudding user response：

You can use:

SELECT 'ab' AS type,
       COUNT(*) AS total
FROM   ( SELECT 1 FROM a UNION ALL
         SELECT 1 from b );
UNION ALL
SELECT 'xy', COUNT(*)
FROM   xy;

CodePudding user response：

You can sum counts from your three unioned Select statements and group the result by combination of sources:

WITH 
    a AS
        ( Select LEVEL "A_ID", 'some column a' "COL_A" From Dual Connect By LEVEL <= 30 ),
    b AS 
        ( Select LEVEL "B_ID", 'some column b' "COL_B" From Dual Connect By LEVEL <= 20 ),
    xy AS 
        ( Select LEVEL "XY_ID", 'some column xy' "COL_XY" From Dual Connect By LEVEL <= 20 )

with above sample data it is like here:

SELECT
    CASE WHEN SOURCE IN('a', 'b') THEN 'ab' ELSE SOURCE END "SOURCE", 
    Sum(CNT) "CNT"
FROM
    (   Select 'a' "SOURCE", Count(*) "CNT" From a Union All
        Select 'b', Count(*) From b Union All
        Select 'xy', Count(*) From xy
    )
GROUP BY 
    CASE WHEN SOURCE IN('a', 'b') THEN 'ab' ELSE SOURCE END
--  
--  R e s u l t :
--  SOURCE        CNT
--  ------ ----------
--  ab             50 
--  xy             20