CODEWITHSUNDEEP
sqldb2case
user3114771
user3114771

Reputation: 13

SQL query to select multiple values

I'm creating a traffic report for my company but I'm really stuck on this piece of code..

I have a report number, and type of accidents denoted by number i.e; 1-slight, 2-serious, 3-fatal and 4-not injured.

usually an accident report contains more than one number, for instance:

a report number 2014123 has a driver with serious injury '2', passenger Not Injured '4'.

so when i fire the select query where report number=2014123 , i get two records, one with '2' injury and the other with '4'.

in the above scenario, the accident is treated as 'Serious' since it contains '2'. '4' is not considered since the passenger is not injured. injury codes with (slight, serious, fatal) are treated higher than (not injured).

How can I generate the report with serious injury (i.e; code '2') and the injury count as '2'(since two records)?

Methods I have tried:

I tried with the SQL Case statement:

(Case WHEN InjuryCode IN ('1','2','4') THEN 'Serious'
WHEN InjuryCode IN ('1','2','3','4') THEN 'FATAL'
WHEN InjuryCode IN ('1','4') THEN 'SLIGHT'

ELSE 'UNKNOWN'
END) AS ACCIDENT_STATUS

but all i got was duplicating and incorrect data.

the injury code is given preference in the following manner: 3>2>1>4

eg: an accident with contains injurycode as:

1,4- slight
2,4- serious
3,4- fatal

1,2,3,4-fatal (because 3 is the highest injury code) etc etc..

I hope this doesn't get you confused, but kindly bear with me, ,i was totally confused at the beginning, but i am actually getting the picture now, although without a solution, please help!

EDIT (the full query from the comment):

SELECT REPORTNUMBER, INJURYCODE,
       (CASE WHEN InjuryCode IN ('1','2','4') THEN 'Serious'
             WHEN INJURYCODE IN ('1','2','3','4') THEN 'FATAL'
             WHEN INJURYCODE IN ('1','4') THEN 'SLIGHT' ELSE 'UNKNOWN'
       END) AS ACCIDENT_STATUS
FROM ACCIDENTS
WHERE REPORTNUMBER=20140302

Upvotes: 1

Views: 4150

Answers (3)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

The use of injuryCode suggests that you need an Injury table (if you don't have one already). Ideally, this includes some sort of severity column that you could order by - something like this:

CREATE TABLE Injury (injuryCode CHAR(1),
                     severity INTEGER,
                     description VARCHAR(20));

INSERT INTO Injury VALUES ('1', 1, 'Slight'),
                          ('2', 2, 'Serious'),
                          ('3', 3, 'Fatal'),
                          ('4', 0, 'Not Injured');

Strictly speaking, what you were attempting before was sorting based on an apparent id of the injury - the only thing ids should be used for is joins, and should otherwise be considered random/undefined values (that is, the actual value is unimportant - it's whether there's anything connected to it that's important). The fact that these happen to be numerical codes (apparently stored as character data - this is perfectly acceptable) is immaterial.

Regardless, with a sorting table defined, we can now safely query the data:

SELECT AggregateAccident.reportNumber, Injury.injuryCode, Injury.description, 
       AggregateAccident.victimCount
FROM (SELECT Accidents.reportNumber, MAX(Injury.severity) as severity, 
             COUNT(*) as victimCount
      FROM Accidents
      JOIN Injury
        ON Injury.injuryCode = Accidents.injuryCode
      GROUP BY Accidents.reportNumber) AggregateAccident
JOIN Injury
  ON Injury.severity = AggregateAccident.severity
ORDER BY AggregateAccident.reportNumber

(And SQL Fiddle example. Thanks to Turophile for the skeleton. Using SQL Server, but this should work on any RDBMS).

EDIT:

If you can't create a permanent table, you can create a temporary one:

WITH Injury AS (SELECT a AS injuryCode, b AS severity, c AS description
                FROM (VALUES ('1', 1, 'Slight'),
                             ('2', 2, 'Serious'),
                             ('3', 3, 'Fatal'),
                             ('4', 0, 'Not Injured')) I(a, b, c))
SELECT AggregateAccident.reportNumber, Injury.injuryCode, Injury.description, 
       AggregateAccident.victimCount
FROM (SELECT Accidents.reportNumber, MAX(Injury.severity) as severity, 
             COUNT(*) as victimCount
      FROM Accidents
      JOIN Injury
        ON Injury.injuryCode = Accidents.injuryCode
      GROUP BY Accidents.reportNumber) AggregateAccident
JOIN Injury
  ON Injury.severity = AggregateAccident.severity
ORDER BY AggregateAccident.reportNumber

(And updated SQL Fiddle)
The WITH clause constructs what's known as a Common Table Expression (CTE), and is basically an inline view or temporary table definition. This could also be done with a subquery, but as I reference Injury twice, using a CTE means I only have to write the contained information once (in cases where the CTE is the result of some other query, this may help performance, too). Most recent/current RDBMSs support this functionality (notably, MySQL does not), including DB2.

Upvotes: 1

Turophile
Turophile

Reputation: 3405

This is not an answer, but an attempt to improve your skills.

If you stored the injury code as a number like this:

 0 = Not injured
 1 = Slight
 2 = Serious
 3 = Fatal

Then you could use this clear and simple SQL:

select reportnumber, max(injurycode) as injurycode, count(*) as involved
from accidents
group by reportnumber

Here is a fiddle to illustrate it: http://sqlfiddle.com/#!2/87faf/1

Upvotes: 0

mustaccio
mustaccio

Reputation: 18945

Something like this? (Not tested).

SELECT REPORTNUMBER,
       CASE INJURYCODE 
           WHEN 1 THEN 'SLIGHT' 
           WHEN 2 THEN 'Serious'
           WHEN 3 THEN 'FATAL'
           ELSE 'UNKNOWN'
       END ACCIDENT_STATUS,
       INJURYCOUNT
FROM (
   SELECT REPORTNUMBER, 
          MAX(CASE INJURYCODE WHEN 4 THEN 0 ELSE INJURYCODE END) INJURYCODE, 
          COUNT(1) INJURYCOUNT,
   FROM ACCIDENTS
   GROUP BY REPORTNUMBER
)

Upvotes: 0

Related Questions