Need Help With Simple MySQL SELECT

Question

I've got a table of crime data. In a simplified version, it would look like this:

Table Headings:

crime_id, neighborhood, offense

Table Data:

 - 1, Old Town,  robbery         
 - 2, Bad Town,    theft        
 - 3, Bad Town,    theft         
 - 4, Uptown,    stolen auto

If I SELECT * FROM mytable WHERE offense ='theft', then the results for Bad Town are returned. But, I'm making a ranking, so what I'm really interested in is:

Old Town: 0  
Bad Town: theft  
Bad Town: theft  
Uptown:   0

How do I write a SELECT statement that returns cases where there are thefts, but also returns neighborhoods that don't have an entry for the specified offense?

UPDATE: This my actual SELECT. I'm having problems applying the solution that p.campbell and Gratzy were so kind to post to this SELECT. How do I apply the CASE statement with the COUNT(*)?

SELECT 
        cbn.neighborhoods AS neighborhoods, 
        COUNT(*) AS offenses, 
        TRUNCATE(((na.neighborhood_area_in_sq_meters /1000) * 0.000386102159),2) AS sq_miles,  
        ( COUNT(*) / ((na.neighborhood_area_in_sq_meters /1000) * 0.000386102159) ) AS offenses_per_sq_mile 
        FROM 
            wp_crime_by_neighborhood cbn, wp_neighborhood_area  na
        WHERE 
            cbn.offense='theft' 
            AND 
            cbn.neighborhoods = na.neighborhoods
        GROUP BY 
            cbn.neighborhoods 
        ORDER BY 
            offenses_per_sq_mile DESC

Tim Gautier · Accepted Answer · 2011-09-01 19:11:08Z

1

If you're looking to make a ranking, wouldn't it be better to get the number of thefts in Bad Town rather than a row for each? Something like this:

select distinct mt.neighborhood, ifnull(total, 0)
from mytable mt
left join (
  select neighborhood, count(*) as total
  from mytable
  where offense = 'theft'
  group by neighborhood
) as t on t.neighborhood = mt.neighborhood

Based on the data you gave, this query should return:

Old Town: 0
Bad Town: 2
Uptown: 0

That seems more useful to me for making a ranking. You can easily throw an order by on there.

answered Sep 1, 2011 at 19:11

Tim Gautier

30.3k5 gold badges50 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laxmidi Over a year ago

Hi, Thanks so much for your suggestion. I like your idea. We're really close to solving this problem. Unfortunately, I also need to return the number of thefts per neighborhood per sq mile which is pulled from the db by ( COUNT(*) / ((na.neighborhood_area_in_sq_meters /1000) * 0.000386102159) ) AS offenses_per_sq_mile (where na is an alias to another table, wp_neighborhood_area). wp_neighborhood_area has a neighborhoods column which we can set equal to mytable mt. Is there a way to join wp_neighborhood_area to your solution? Thank you!

Laxmidi Over a year ago

Hi Tim, Thanks so much for the help. I figured out how to add one more left join to your solution: SELECT distinct mt.neighborhoods, ifnull(total, 0) AS total, ( total / ((neighborhood_area_in_sq_meters /1000) * 0.000386102159) ) AS offenses_per_sq_mile FROM wp_crime_by_neighborhood mt LEFT JOIN ( select neighborhoods, count(*) as total from wp_crime_by_neighborhood where offense = 'theft' group by neighborhoods ) as t on t.neighborhoods = mt.neighborhoods LEFT JOIN wp_neighborhood_area ON mt.neighborhoods = wp_neighborhood_area.neighborhoods Awesome! Thank you!!

Gratzy · Accepted Answer · 2011-09-01 18:16:17Z

1

I would think using a case statement should do it.

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

something like

Select neighborhood, 
case offense when 'theft' then offense  else '0' end case 
from table

answered Sep 1, 2011 at 18:16

Gratzy

9,4174 gold badges32 silver badges45 bronze badges

1 Comment

Laxmidi Over a year ago

Thank you for your post. I've posted the actual SELECT statement that I'm working on. Unfortunately, I don't know how to implement the solution that you and p.campbell suggested in my problem. Any suggestions? Thanks so much for your help!

p.campbell · Accepted Answer · 2011-09-01 21:59:59Z

1

Try this:

SELECT  cbn.neighborhoods AS neighborhoods,  
    CASE WHEN IFNULL(COUNT(*),0) > 0 THEN CONCAT(COUNT(*), ' ', offense)
         ELSE  '0' 
    END AS offenses
    --- ... and the rest of your query

    FROM        wp_crime_by_neighborhood cbn
    INNER JOIN  wp_neighborhood_area  na  
        ON      cbn.neighborhoods = na.neighborhoods 
    WHERE       cbn.offense='theft'                     
    GROUP BY    cbn.neighborhoods  
    --ORDER BY  offenses_per_sq_mile DESC

edited Sep 1, 2011 at 21:59

answered Sep 1, 2011 at 18:14

p.campbell

101k70 gold badges264 silver badges326 bronze badges

2 Comments

Laxmidi Over a year ago

Thank you very much for your suggestion. Unfortunately, I am really thick as I haven't got it working in my actual SELECT. I went ahead and posted my actual 'working' SELECT statement. How would I apply your solution in my case? Can CASE be used in conjunction with COUNT(*)? Thank you for your kind help!

Laxmidi Over a year ago

@pcampbell: Thanks so much for the help. I tried the new version, but it gave me: #1054 - Unknown column 'offenses_per_sq_mile' in 'order clause' because the offenses_per_sq_mile isn't a column, it's the long calculation on Line 5 in my SELECT statement. I tried it without the ORDER BY just to see what would happen. I got the neighborhoods, but the offenses has the number and type of offense in one column like "24THEFT", "13THEFT", etc. If you have other ideas, please let me know. Thank you for helping me.

Collectives™ on Stack Overflow

Need Help With Simple MySQL SELECT

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related