Combine 1 row into 2

[db2mo]

Hi,
I have a statement where I'm trying to combine the data of two rows into one. My data has to do with crash circumstances. I am selecting the cicumstance and the accident no. Circumstance '04' and '05' are the same. I want to combine all the instances where there is a circumstance '04' and '05' with the same accident number. Since I'm combining them, I then only want to count that accident one time. Below is the code I have so far any suggestions would be greatly appreciated.

SELECT QO05DR_VH_CONT_CIRC  ,  COUNT(DISTINCT a.QO01ACCIDENT_NO) 
FROM XTECH.TR10TBACCIDENT AS a  
JOIN XTECH.TR10TBVEHICLE_CIRCUMSTANCES AS vc ON vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO
WHERE (QO01ACCIDENT_YEAR = '2008' OR QO01ACCIDENT_YEAR = '2009' OR QO01ACCIDENT_YEAR = '2010' )
AND (QO01LONG_OR_SHORT_FORM = 'L' OR QO01LONG_OR_SHORT_FORM = 'S') 
AND (QO01SUBMIT_AGENCY_ORI = 'xxxxxxxxx' OR QO01MUNICIPALITY = 'xxxx')

GROUP BY QO05DR_VH_CONT_CIRC

 

[blom0344]

You could use the comcept of inline views here:

SELECT ...........
FROM
(SELECT ......... FROM .........) TEMP1,
(SELECT ......... FROM .........) TEMP2
ON TEMP1.QO01ACCIDENT_NO = TEMP2.QO01ACCIDENT_NO

where you can define the temp sets to generate 1 row for each QO01ACCIDENT_NO


Ties Blom