CODEWITHSUNDEEP
oracle-database
Sri
Sri

Reputation: 179

How to get count by using UNION operator

i'm trying to get total count by using UNION operator but it gives wrong count.

select count(*) as companyRatings from (
select count(*) hrs from (
select distinct hrs from companyA
)
union           
select count(*) financehrs from (
select distinct finance_hrs from companyB
)
union           
select count(*) hrids from (
select regexp_substr(hr_id,'[^/]+',1,3) hrid from companyZ
)
union           
select count(*) cities from (
select regexp_substr(city,'[^/]+',1,3) city from companyY
)
);

individual query's working fine but total count not matching.

individual results here: 12 19 3 6 present total count: 31

Actual total count:40. so there is any alternate solution without UNION operator?

Upvotes: 0

Views: 7128

Answers (2)

Utsav
Utsav

Reputation: 8103

Edit. Updated query using Sum

select sum(cnt) as companyRatings from 
(
    select count(*) as cnt from (select distinct hrs from companyA)
    union all          
    select count(*) as cnt from (select distinct finance_hrs from companyB)
    union all        
    select count(*) as cnt from (select regexp_substr(hr_id,'[^/]+',1,3) hrid from companyZ)
    union all       
    select count(*) as cnt from (select regexp_substr(city,'[^/]+',1,3) city from companyY)
)

Previous answer:

Try this

SELECT (
        SELECT count(*) hrs
        FROM (
            SELECT DISTINCT hrs
            FROM companyA
            )
        ) 
        + 
        (
        SELECT count(*) financehrs
        FROM (
            SELECT DISTINCT finance_hrs
            FROM companyB
            )
        ) 
        +
        (
        SELECT count(*) hrids
        FROM (
            SELECT regexp_substr(hr_id, '[^/]+', 1, 3) hrid
            FROM companyZ
            )
        )
        + 
        (
        SELECT count(*) cities
        FROM (
            SELECT regexp_substr(city, '[^/]+', 1, 3) city
            FROM companyY
            )
        ) 
AS total_count
FROM dual

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

To add values you'd use +. UNION is to add data sets.

select
  (select count(distinct hrs) from companyA)
  +
  (select count(distinct finance_hrs) from companyB)
  +
  (select count(regexp_substr(hr_id,'[^/]+',1,3)) from companyZ)
  +
  (select count(regexp_substr(city,'[^/]+',1,3)) from companyY)
  as total
from dual;

But I agree with juergen d; you should not have separate tables per company in the first place.

Upvotes: 1

Related Questions