Creating Subtotals & Grand Total in Answer set 1

[Queryman]

I have a query that I am going to give to an user who is not that technically inclined. He will be using Queryman. The query retrieves some values from Teradata, but the end report the user wants needs to have subtotals and grand total for the first field in the query, there are sixteen unique values for chapter_id. Can this be done using a sub-select query? Here is the query I have that retrives the values needed, the user needs all this info, plus subtotals for each chapter_id and a grand total.
select
case when substr(CHAPTER.CHAPTER_ID,1,2) ='01' THEN '01: ANTI INFECTIVES'
when substr(CHAPTER.CHAPTER_ID,1,2) ='02' THEN '02: ANTINEOPLASTICS IMMUNOSUPPRESSANT'
when substr(CHAPTER.CHAPTER_ID,1,2) ='03' THEN '03: AUTONOMIC CNS DRUGS NEUROLOGY'
when substr(CHAPTER.CHAPTER_ID,1,2) ='04' THEN '04: CARDIOVASCULAR HYPERTENSION LIPID'
when substr(CHAPTER.CHAPTER_ID,1,2) ='05' THEN '05: DERMATOLOGICALS TOPICAL THERAPY'
when substr(CHAPTER.CHAPTER_ID,1,2) ='06' THEN '06: EAR NOSE THROAT MEDICATIONS'
when substr(CHAPTER.CHAPTER_ID,1,2) ='07' THEN '07: ENDOCRINE DIABETES'
when substr(CHAPTER.CHAPTER_ID,1,2) ='08' THEN '08: GASTROENTEROLOGY'
when substr(CHAPTER.CHAPTER_ID,1,2) ='09' THEN '09: IMMUNOLOGY VACCINES BIOTECHNOLOGY'
when substr(CHAPTER.CHAPTER_ID,1,2) ='10' THEN '10: MUSCULOSKELETAL RHEUMATOLOGY'
when substr(CHAPTER.CHAPTER_ID,1,2) ='11' THEN '11: OBSTETRICS GYNECOLOGY'
when substr(CHAPTER.CHAPTER_ID,1,2) ='12' THEN '12: OPHTHAMOLOGY'
when substr(CHAPTER.CHAPTER_ID,1,2) ='13' THEN '13: RESPIRATORY ALLERGY COUGH COLD'
when substr(CHAPTER.CHAPTER_ID,1,2) ='14' THEN '14: UROLOGICALS'
when substr(CHAPTER.CHAPTER_ID,1,2) ='15' THEN '15: VITAMINS HEMATINICS ELECTROLYTES'
when substr(CHAPTER.CHAPTER_ID,1,2) ='16' THEN '16: DIAGNOSTICS MISCELLANEOUS'
END AS CHAPTER_ID,
DrugCurr.BRAND_NME,
COUNT (distinct Claim.PATIENT_ID) as PATIENT_COUNT,
SUM (Claim.CLAIM_COUNT_NBR)AS TOTAL_CLAIMS,
SUM (Claim.bil_final_ingredient_cost_amt) AS TOTAL_INGR_COST
from
IW_DEFLT_PRODDB_V.MEDICAL_PRODUCT_CURRENT DrugCurr,
IW_Deflt_proddb_V.CHAPTER Chapter,
IW_Deflt_proddb_V.DRUG_FORMULARY_MAP FormMap,
IW_Deflt_PRODDB_V.HIST_PHARMACY_CLAIM_OTH_ALL Claim

where Claim.CARRIER_OPERATIONAL_ID = '1491'
and Claim.claim_client_dollar_impact_cde = '2'
and Claim.SERVICED_DTE BETWEEN '2003-09-01' and '2003-09-30'
and Claim.MAIL_RETAIL_CDE in('M')
and Claim.bil_product_service_id = DrugCurr.product_service_id
and CHAPTER.CHAPTER_ID = FORMMAP.MED_FORMULARY_CHAPTER_1_ID
and FORMMAP.product_service_id = DRUGCURR.product_service_id
group by 1,2 order by 1,2;



Michael

 

[RogerCruse]

Hi Michael,
Why not use UNION like this

select CHAPTER_ID, BRAND_NAME, COUNT(blah), SUM(blah)
FROM blah, blah, blah
UNION
select CHAPTER_ID, '99: Sub Total', COUNT(blah), SUM(blah)
FROM blah, blah, blah
UNION
select '99: Grand Total','======', COUNT(blah), SUM(blah)
FROM blah, blah, blah
GROUP BY 1,2
ORDER BY 1,2

Roger...

 

[Queryman]

Thanks Roger, I will try that.



Michael

 

[Queryman]

Works great, thanks!



Michael

 

[Romary]

Shouldn't you exclude external claims in this query?

 

[Queryman]

Why do you ask?



Michael