1

Hi I have 2 table Offense table and User_jobs table 

offense table:
crime_id |crime_type |casenumber|
---------+-----------+----------+
1        | 3         |1         |
2        | 3         |1         |
1        | 3         |2         |
12       | AA        |2         |

user_jobs table:
casenumber |disposal_status |
-----------+----------------+
1          | yes            |
1          | yes            |
2          | no             |
2          | no             |

what i want is to count the number of rows with the same combination say crime_id=1 and crime_type= 3 but these must have a disposal status of yes in the user_jobs table.

i want to do this in mysql. pliz help

sorry but i am new to mysql. i now want to display the real names of those id not the id themselves.

the tables with these IDs are crime_category and Crime_type Crime_catgory 

table: 
category |crime_id | 
-----------+----------------+ 
theft | 1 | 
murder | 2 | 
rape | 3 | 2 | 
no | 

Crime_type table: 
Crime_type |id | 
---------------+----------------+ 
administrative | yes | 
criminal | yes |
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
Tapiwa Takaindisa
  • 45
  • 4

1 Answers1

1

You can do this with a simple inner join and an aggregate function:

select
    o.crime_id,
    o.crime_type,
    count(*)
from
    offence o
        join user_jobs uj
            on o.casenumber=uj.casenumber
where
    uj.disposal_status='Yes'
group by
    o.crime_id,
    o.crime_type

This will pick up distinct combinations of the first two columns joined as they should tot he jobs table and only where the disposal_status is equal to 'Yes'

Edit: You would probably do really well to have a read of this Q&A that I put together for exactly this sort of situation - where I give you the code for it, but would like to explain this is a lot more detail. The Q&A explains why this type of thing (and many many others) work and how they do so:

How can an SQL query return data from multiple tables

Edit 2:

select
    o.crime_id,
    o.crime_type,
    ct.category,
    count(*)
from
    offence o
        join user_jobs uj
            on o.casenumber=uj.casenumber
        join crime_type ct
            on o.crime_type=ct.crime_id
where
    uj.disposal_status='Yes'
group by
    o.crime_id,
    o.crime_type,
    ct.category,
Community
  • 1
  • 1
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • @TapiwaTakaindisa No problems, (as this is your first question) if this has solved your issue, it is polite to Accept an Answer so that other folks don't waste time trying to answer it. You can do that by pressing the tick next to the answer. – Fluffeh Apr 05 '14 at 09:10
  • sorry but i am new to mysql. i now want to display the real names of those id not the id themselves. the tables with these IDs are crime_category and Crime_type Crime_catgory table: category |crime_id | -----------+----------------+ theft | 1 | murder | 2 | rape | 3 | 2 | no | Crime_type table: Crime_type |id | ---------------+----------------+ administrative | yes | criminal | yes | – Tapiwa Takaindisa Apr 05 '14 at 09:35
  • @TapiwaTakaindisa I have added your comment to the question (it is better to edit the question in cases like this) as well as adding another edit to my answer which joins the next table as best I could make it out from the comment. – Fluffeh Apr 05 '14 at 09:48