Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    5

    Unanswered: Combine 2 rows into 1

    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

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Talking I like simple queries

    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
    Last edited by Lenny77; 04-13-11 at 14:22.

  3. #3
    Join Date
    Mar 2011
    Posts
    5

    Thank You!!

    Thanks so much Lenny77! That did what I needed.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Smile

    Quote Originally Posted by tonkuma View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •