CODEWITHSUNDEEP
sqlselectcaseteradata
Z Pal
Z Pal

Reputation: 25

SQL: Using case expression to compare values from column Conditional on Values of Other Column

For my example, I have (fake) crime data with three columns: city, number of crimes committed, and time period (containing time periods 1 and 2). I need to create a table with city as one column and crime_reduced as another which is an indicator for whether the crimes committed decreased from time period 1 to period 2.

How may I setup condition to test that crimes_committed in period 2 are less than crimes_committed in period 1? My constraint is that I cannot save a physical copy of a table, so I cannot split my table into one with time period 1 and the other with time period two. I tried the follow code with a case expression, which in retrospect makes no sense.

SELECT city,
CASE WHEN time_period = 1 AND crimes_committed > time_period = 2 
         AND crimes_committed THEN 1 
     ELSE 0 END AS crime_reduced
FROM crime_data
GROUP BY city;

Edit: Unfortunately, I couldn't get the case sign expression to work (it might be a platform problem). Though that lead to this question -- is there any way to embed a case expression within a case (this would allow for proper results without creating subqueries)? Something that would look like below (this does not work in Teradata):

SELECT city,
SUM(CASE WHEN 
       (CASE WHEN time_period = 1 THEN crimes_commited END) > (CASE WHEN time_period = 2  
        THEN crimes_committed END) 
    THEN 1 ELSE 0 END) AS crime_reduced
FROM crime_data
GROUP BY city;

Upvotes: 1

Views: 11989

Answers (2)

dnoeth
dnoeth

Reputation: 60472

You need a conditional CASE:

SELECT city,
   CASE SIGN(-- data for 1st period
               MAX(CASE WHEN time_period = 1 THEN crimes_committed END)             -- data for 2nd period
             - MAX(CASE WHEN time_period = 2 THEN crimes_committed END))
       WHEN  0 THEN 'Same'
       WHEN  1 THEN 'Decreased'
       WHEN -1 THEN 'Increased'
       ELSE 'Unkown (no data)'
      END 
FROM crime_data
GROUP BY city;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311498

You could join two sub queries of the table, each querying a different period:

SELECT t1.city,
       CASE WHEN t1.crimes_committed > t2.crimes_committed THEN 'Yes'
                                                            ELSE 'No'
       END AS crimes_reduced
FROM   (SELECT city, crimes_committed
        FROM   crime_data
        WHERE  period = 1) t1
JOIN   (SELECT city, crimes_committed
        FROM   crime_data
        WHERE  period = 2) t2 ON t1.city = t2.city

Upvotes: 2

Related Questions