Rolling up subtypes within a Grouping 1

[weberm]

I am writing a query for a report that reads a table containing receipts and gives totals by payment type:

SELECT CASE
         WHEN payment_type_an = 'A' THEN 'AMEX       '
         WHEN payment_type_an = 'C' THEN 'CASH/CHECK '
         WHEN payment_type_an = 'D' THEN 'DISCOVER   '
         WHEN payment_type_an = 'E' THEN 'ECHECK     '
         WHEN payment_type_an = 'M' THEN 'MONEY ORDER'
         WHEN payment_type_an = 'V' THEN 'VISA       '
         WHEN payment_type_an IS NULL THEN 'UNKNOWN'
       END "PT",
       COUNT (*) "COUNT",
       SUM (total_application_services_amt) "SUM"
  FROM closeout_detail
 WHERE processor_source = in_rec.PROCESSOR_SOURCE
   AND proc_file_seq_nbr = in_rec.PROC_FILE_SEQ_NBR
 GROUP BY payment_type_an
 ORDER BY payment_type_an;

This produces a line for each payment type, but how would one rollup the credit card values ("A","D",'V") into one entry?

 

[SantaMufasa]

To help us help you, weberm, could you please post the code to CREATE TABLE and INSERT INTO <table>, along with the output you expect to see from those rows?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[weberm]

Well, I didn't want to make it overly complicated , but for purposes of my question, the table Closeout_Detail contains payment transactions with a unique payment identifier, payment amounts, payment type, etc. For example, this table would contain the following rows:

PID	PAYMENT TYPE	CARDHOLDER_NM	TOTAL_APPLICATION_SERVICES_AMT
8509112		A	PERSIS WHITEHOUSE	100
8509400		A	JAMES GREER		100
8504704		V	KEVIN HOPE		100
8505744		M	LAURIE COE		100
8502344		M	JENNIFER GASTER		100
8507432		V	JOHN G WELLS		100
8507192		M	DAVID K. READING	100
8507104		V	DAMON HARRIS		100

My report using the query above returns this:

AMEX  		2 records totaling	 $200.00
MONEY ORDER	3 records totaling	 $300.00
VISA  		3 records totaling	 $300.00

This is how I wish the output to appear:

CREDIT CARD  	5 records totaling	 $500.00
MONEY ORDER    	3 records totaling	 $300.00

I thought I could replace all the WHEN credit card types with a single one (IN ('A','D', 'V')), but it repeats the credit card aggregate for each individual payment type:

MONEY ORDER    	3 records totaling	 $300.00
CREDIT CARD  	5 records totaling	 $500.00
MONEY ORDER    	3 records totaling	 $300.00

So close, yet so far!

 

[taupirho]

SQL> select * from tom;

ID AMT T
---------- ---------- -
1 100 A
2 100 A
3 100 V
4 100 M
4 100 M
5 100 V
6 100 M
7 100 V

8 rows selected.

SQL> select pt2,sum(amt),count(*) from
2 (
3 SELECT CASE
4 WHEN type = 'A' THEN 'AMEX '
5 WHEN type = 'C' THEN 'CASH/CHECK '
6 WHEN type = 'D' THEN 'DISCOVER '
7 WHEN type = 'E' THEN 'ECHECK '
8 WHEN type = 'M' THEN 'MONEY ORDER'
9 WHEN type = 'V' THEN 'VISA '
10 WHEN type IS NULL THEN 'UNKNOWN'
11 END "PT",
12 case
13 WHEN type in( 'A','D','V') THEN 'CREDIT' ELSE 'NOT CREDIT'
14 end PT2 , amt
15 FROM tom
16 )
17 having pt2 is not null group by PT2
18
SQL> /

PT2 SUM(AMT) COUNT(*)
---------- ---------- ----------
CREDIT 500 5
NOT CREDIT 300 3


In order to understand recursion, you must first understand recursion.

 

[weberm]

Ahhh, the solution involved a nested SELECT statement! Thank you!