Hi,

The following statement was intended to get total count from each
section first and then get the total counts of all sections. The
union part works and returns. But 'select sum(used)' does not. The
error message can be found at the end of this message. Appreciate any
help! Thanks.

------
used
47
73
-----

select sum(used)
from (
select count(*) as "used"
from ur.agncy
where agncy_id >= '2283' and agncy_id <= '2329'

union

select count(*) as "used"
from ur.agncy
where agncy_id >= '2922' and agncy_id <= '2994'
)

An error occurred while running the query.

Illegal symbol "<EMPTY>", Some symbols that might be legal are:
CORRELATION NAME.

(SQL code = -104, SQL state =42601)

The reason of the message would be missing correlation clause.

Try to add "AS q" like in the following example.
("q" could be any valid correlation-name.)

select sum(used)
from (
select count(*) as "used"
from ur.agncy
where agncy_id >= '2283' and agncy_id <= '2329'
union
select count(*) as "used"
from ur.agncy
where agncy_id >= '2922' and agncy_id <= '2994'
) AS q

1) Possible syntax error.
SELECT SUM(used) should be SELECT SUM("used")
or
remove double quotations from as "used" in subquery.

2) Possible incorrect result.
UNION should be UNION ALL.
If first and second select in subquery returned same count,
final result SUM(used) would return the count.
(Not two times of the count.)

3) You can use BETWEEN expression.

Here is an example of rewrite.
SELECT SUM(used)
FROM (
SELECT COUNT(*) as used
FROM ur.agncy
WHERE agncy_id BETWEEN '2283' AND '2329'
UNION ALL
SELECT COUNT(*) as used
FROM ur.agncy
WHERE agncy_id BETWEEN '2922' AND '2994'
) AS q
;

SELECT COUNT(*)
FROM ur.agncy
WHERE agncy_id BETWEEN '2283' AND '2329'
OR agncy_id BETWEEN '2922' AND '2994'
;

Get "count from each section" and "total counts" at once.

SELECT CASE GROUPING(section)
WHEN 1 THEN
'All sctions'
ELSE section
END AS section
, SUM(used) AS count
FROM (
SELECT 'Section one'
, COUNT(*)
FROM ur.agncy
WHERE agncy_id BETWEEN '2283' AND '2329'
UNION ALL
SELECT 'Section two'
, COUNT(*)
FROM ur.agncy
WHERE agncy_id BETWEEN '2922' AND '2994'
) AS q (section , used)
GROUP BY
ROLLUP(section)
;

On Mar 12, 1:20*pm, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
Thanks very much of the help. Also really appreciated providing other
options!

Bing

On Mar 13, 4:20*am, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
SELECT COUNT(*) AS "All sections"
, COUNT(CASE
WHEN agncy_id BETWEEN '2283' AND '2329' THEN 0
END
) AS "Section one"
, COUNT(CASE
WHEN agncy_id BETWEEN '2922' AND '2994' THEN 0
END
) AS "Section two"
FROM ur.agncy
WHERE agncy_id BETWEEN '2283' AND '2329'
OR agncy_id BETWEEN '2922' AND '2994'
;