Problems with "WHERE" statement

db2mo · #1 (**permalink**) 06-24-11, 12:53

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.

Code:

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)

Pat Phelan · #2 (**permalink**) 06-24-11, 13:36

If I had to make a guess, I'd say that your SQL is for DB2 instead of Microsoft SQL Server. If it is, please let me know and I'll move this discussion thread to the DB2 forum where it will have a better chance of getting attention from people with DB2 expertise.

If I'm correct in the DB2 assumption, then I'm 99.5% certain that all you really need is the addition of the DISTINCT keyword, like so:

Code:

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(DISTINCT 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)

Please let me know if I'm correct in the DB2 assumption, and also if using DISTINCT solves your problem.

-PatP

db2mo · #3 (**permalink**) 06-24-11, 14:12

That did it! I feel like an idiot since it was that simple. Yes you are correct. I am creating the report in SSRS, but I am hitting a DB2 database. Thanks again for your help!

tonkuma · #4 (**permalink**) 06-24-11, 22:39

I thought that no subquery nor CUBE were not necessary, like ...

Code:

SELECT COUNT(DISTINCT QO01ACCIDENT_NO) AS ACC_CNT
     , CASE QO01ACCIDENT_SEVERITY
       WHEN '1' THEN 'Fatal' 
       WHEN '2' THEN 'Personal Injury' 
       WHEN '3' THEN 'Property Damage'
       END AS SEV_ACC
     , CASE QO01ACCIDENT_TYPE
       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  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 IN ('04' , '05')
 GROUP BY
       QO01ACCIDENT_SEVERITY
     , QO01ACCIDENT_TYPE
 ORDER BY
       QO01ACCIDENT_SEVERITY ASC
     , QO01ACCIDENT_TYPE     ASC
;

Internet Services

Web Development

Digital Marketing

Consumer Tech