June 24, 2011 at 11:01 am
I am trying to create a statement that will combine two fields if they have the same accident number. So basically I have an accident report table that has a field for accident number and circumstance. There are two types of speeding circumstances ('04' and '05). If an accident report has both a '04' and a '05' speeding circumstance, I only want that circumstance counted once instead of twice even though two circumstances were reported. How do I tell my statement to do that? Here is the current statement I have. It counts all circumstances even if they have the same accident number.
SELECT CNT AS ACC_CNT, (CASE SEVERITY WHEN '1' THEN 'Fatal' WHEN '2' THEN 'Personal Injury' WHEN '3' THEN 'Property Damage' END) AS SEV_ACC,
(CASE ACCTYP WHEN '01' THEN 'Animal' WHEN '02' THEN 'Bicyclist' WHEN '03' THEN 'Fixed Object' WHEN '04' THEN 'Other Object' WHEN '05' THEN 'Pedestrian' WHEN '06' THEN 'Train'
WHEN '07' THEN 'Vehicle In Transport' WHEN '08' THEN 'Vehicle On Other Roadway' WHEN '09' THEN 'Parked Vehicle' WHEN '10' THEN 'Non-Collision Overturn' WHEN '11' THEN 'Non-Collision Other'
ELSE '0' END) AS ACC_TYP
FROM TABLE
(SELECT COUNT(QO01ACCIDENT_NO) AS CNT, ACCTYP, SEVERITY, GROUPING(ACCTYP) AS ACCTYP_GROUP, GROUPING(SEVERITY) AS SEVERITY_GROUP
FROM TABLE
(SELECT QO01ACCIDENT_NO, QO01ACCIDENT_SEVERITY AS SEVERITY, QO01ACCIDENT_TYPE AS ACCTYP
FROM XTECH.TR10TBACCIDENT a, XTECH.TR10TBVEHICLE_CIRCUMSTANCES vc
WHERE (QO01ACCIDENT_YEAR BETWEEN ? AND ?) AND (QO01SUBMIT_AGENCY_ORI = ? OR
QO01MUNICIPALITY = ?) AND (vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO) AND (QO05DR_VH_CONT_CIRC = '04' OR QO05DR_VH_CONT_CIRC = '05')) AS SEVACC
GROUP BY CUBE(ACCTYP, SEVERITY)
ORDER BY ACCTYP ASC, SEVERITY ASC) AS STAT
WHERE (ACCTYP_GROUP = 0 AND SEVERITY_GROUP = 0)
June 30, 2011 at 2:45 am
One possibility would be to use 2 queries - one for types 04 and 05 only, and one for everything else - joined with a UNION ALL.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply