CODEWITHSUNDEEP
sqloracle-database
bestie techhy
bestie techhy

Reputation: 11

oracle count query with union

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;

Sample result for 1st query:

COUNT
10
40

2nd query

select count(*) from xy;

Sample result for 2nd query:

COUNT
20

I want output like this in 2 rows:

TABLES COUNT
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.

Upvotes: 1

Views: 100

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Assuming that your real queries can be a lot more complex, I take it as a given that we shall not try to change them and somehow merge or split them.

Your first query returns two rows. You want to get their sum, so you must aggregate the result and use SUM.

Below query uses CTEs (subqueries in the WITH clause) for your two queries, and then a query that gets this sum. It then uses these CTEs for the final UNION ALL query.

with query1 (cnt) as (select count(*) from a union all select count(*) from b)
   , query2 (cnt) as (select count(*) from xy)
   , sumquery1 (total) as (select sum(cnt) from query1)
select 'ab' as tables, total from sumquery1
union all
select 'xy' as tables, cnt from query2
order by tables desc;

Upvotes: 0

d r
d r

Reputation: 7836

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

Upvotes: 0

MT0
MT0

Reputation: 168416

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;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143023

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;

Upvotes: 1

Related Questions