Oracle SQL Pivot -- Getting Row Totals

Question

I have the following query:

SELECT *
FROM

(
SELECT COUNT (APP_AUTOMATIC_NUMBER) AS RECORD_COUNT, LETTER_TYPE_ID, 
TO_DATE (EXTRACT_DATE, 'MMDDYYYY') AS EXTRACT_DATE,
'Consumer' AS ACCT_TYPE

FROM [TABLE_NAME]

WHERE SUBSTR(EXTRACT_DATE, -4) = '2020' and EXTRACT_DATE between TO_CHAR( (sysdate - 6), 'MMDDYYYY' ) and TO_CHAR( (sysdate - 4), 'MMDDYYYY' )

GROUP BY CUBE (LETTER_TYPE_ID, EXTRACT_DATE)
) t

PIVOT(
    SUM(RECORD_COUNT) 
    FOR LETTER_TYPE_ID IN (
        1,10,11,12,13,14,4,7,9)
)
ORDER BY EXTRACT_DATE

It will return the results in the screenshot (see belwow) where it's totaling the column but I'd like to total over the row instead. So getting the total record count for each day instead ... and not showing the total by column at all.

This seems like a common issue however I'm crushed trying to search the internet for a straight forward answer.

user_10676716 · Answer

I would recommend conditional aggregation: it is more flexible than the vendor-specific pivot syntax, which makes it easier to do what you want.

select 
    to_date (extract_date, 'mmddyyyy') as extract_date,
    'Consumer' as acct_type
    sum(case when letter_type_id =  1 then 1 else 0 end) as type_1,
    sum(case when letter_type_id = 10 then 1 else 0 end) as type_10,
    ...
    sum(case when letter_type_id =  9 then 1 else 0 end) as type_9,
    count(*) type_total
from mytable 
where 
    letter_type_id in ( 1,10,11,12,13,14,4,7,9)
    and extract_date between to_char(sysdate - 6, 'mmddyyyy') and to_char(sysdate - 4, 'mmddyyyy')
    and substr(extract, -4) = '2020'
group by 1
order by 1

Side note: I am unclear about the filtering logic on the date in the where clause: your current predicates will filter out all rows when the system date of your server will reach January 6th, 2021.

Oracle SQL Pivot -- Getting Row Totals

1 Answers