SQL: GROUP BY and UNION addition issue..
I'm looking to to get the TOTALS of the grouping. What I'm getting now is either a 1 or a 2 whether or not each PRIMARY_ASSIGMENT_GROUP appears in each query. when I run the
SELECT a.PRIMARY_ASSIGNMENT_GROUP,GROUP(a.PRIMARY_ASSIGNMENT_GROUP) from
(
SELECT
COUNT(PRIMARY_ASSIGNMENT_GROUP),PRIMARY_ASSIGNMENT_GROUP
from
SMINCREQ
LEFT JOIN SMOPERATOR on SMINCREQ.OPENED_BY=SMOPERATOR.NAME
WHERE
open_time BETWEEN to_date(:P246_SDATEB,'DD-MON-YYYY') AND to_date(:P246_EDATEB,'DD-MON-YYYY') and
((to_char(CAST((FROM_TZ(CAST(open_time AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'HH24') between '00' and '08') or (to_char(CAST((FROM_TZ(CAST(open_time AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'HH24') between '19' and '24')) GROUP BY PRIMARY_ASSIGNMENT_GROUP
UNION ALL
SELECT
COUNT(PRIMARY_ASSIGNMENT_GROUP),PRIMARY_ASSIGNMENT_GROUP
from SMINTERACTIONS
WHERE
open_time BETWEEN to_date(:P246_SDATEB,'DD-MON-YYYY') AND to_date(:P246_EDATEB,'DD-MON-YYYY') and
((to_char(CAST((FROM_TZ(CAST(open_time AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'HH24') between '00' and '08') or (to_char(CAST((FROM_TZ(CAST(open_time AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'HH24') between '19' and '24')) GROUP BY PRIMARY_ASSIGNMENT_GROUP
) a
GROUP BY (a.PRIMARY_ASSIGNMENT_GROUP)
ORDER by COUNT(a.PRIMARY_ASSIGNMENT_GROUP)