Combine 2 rows into 1

db2mo · #1 (**permalink**) 04-13-11, 12:04

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.

Code:

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

Lenny77 · #2 (**permalink**) 04-13-11, 13:14

Maybe this one will work better ?

Code:

SELECT 

case QO05DR_VH_CONT_CIRC
when '04' then '05'
else 
QO05DR_VH_CONT_CIRC
end as Circumstance
,  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 in ('2008', '2009', '2010')  
AND QO01LONG_OR_SHORT_FORM in  ('L', 'S') 
AND (QO01SUBMIT_AGENCY_ORI = 'xxxxxxxxx' OR QO01MUNICIPALITY = 'xxxx')

GROUP BY 
case QO05DR_VH_CONT_CIRC
when '04' then '05'
else 
QO05DR_VH_CONT_CIRC
end

Lenny

db2mo · #3 (**permalink**) 04-15-11, 09:54

Thanks so much Lenny77! That did what I needed.

tonkuma · #4 (**permalink**) 04-15-11, 10:13

Just for fun.

Code:

COALESCE( NULLIF( QO05DR_VH_CONT_CIRC , '04' ) , '05' )

would be equivalent to

Code:

case QO05DR_VH_CONT_CIRC
when '04' then '05'
else 
QO05DR_VH_CONT_CIRC
end

Lenny77 · #5 (**permalink**) 04-15-11, 10:21

Quote:

Originally Posted by tonkuma

Just for fun.

Code:

COALESCE( NULLIF( QO05DR_VH_CONT_CIRC , '04' ) , '05' )

would be equivalent to

Code:

case QO05DR_VH_CONT_CIRC
when '04' then '05'
else 
QO05DR_VH_CONT_CIRC
end

For me looks better this one:

Code:

IFNULL ( NULLIF( QO05DR_VH_CONT_CIRC , '04' ) , '05' )

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech