CODEWITHSUNDEEP
sqloracle-databaseoracle12c
en Lopes
en Lopes

Reputation: 2113

Oracle sum from different tables

I want to sum values from different tables as follows in an Oracle DataBAse from the PL/SQL Developer, so I prepared this SQL statement:

select sum(total) as ttt from
(select count('1') as total 
from vehicle_hotel 
union
select count('1') as total 
from alarm
union
select count('1') as total
from vd_poi
union
select count('1') as total
from person_hotel
union
select count('1') as total
from social_office_transaction
union
select count('1') as total
from person_hotel_field_value
union
select count('1') as total
from pd_trf_week
union
select count('1') as total
from aggreg_exception
union
select count('1') as total
from pd_week_rec;
select count('1') as total 
from hist_pd_week_rec
union
select count('1') as total
from pd_week);

But I got this error:

00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error en la línea: 32, columna: 12

Upvotes: 0

Views: 1294

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Your query has a handful of problems, which I fixed, listed below in descending order of bad to less bad:

  • you had a stray semicolon inside the subquery from pd_week_rec; ... this is likely the cause of the particular error you were seeing
  • you were using UNION between the subqueries, which could result in incorrect results if two subqueries just happened to have the same count by coincidence
  • you might want to assign an alias to your derived table (required by certain versions of SQL)
  • you were using COUNT('1'), perhaps not wrong but I would use COUNT(*) instead


select sum(total) as ttt
from
(
    select count(*) as total 
    from vehicle_hotel 
    union all
    select count(*)
    from alarm
    union all
    select count(*)
    from vd_poi
    union all
    select count(*)
    from person_hotel
    union all
    select count(*)
    from social_office_transaction
    union all
    select count(*)
    from person_hotel_field_value
    union all
    select count(*)
    from pd_trf_week
    union all
    select count(*)
    from aggreg_exception
    union all
    select count(*)
    from pd_week_rec
    select count(*)
    from hist_pd_week_rec
    union all
    select count(*)
    from pd_week
) t;

Upvotes: 2

Related Questions