sqlchallenges

A Table DIVISION (divid, divname ) have many units, UNIT (unitid, unitname, divid[fk] ) have many locations, LOCATION ( locid, locname, unitid[fk]) have many criminals, CRIMINAL (criminalid, fname, lname, locid[fk]) Can anyone give me a SQL QUERY . I need 2 fields only one is DIVISION and other is VAL(with values the sum of each criminal in a particular division). Division count ---------------- Div1 5 Div2 10 Answer: SELECT d.divname, count( d.divid ) FROM division d JOIN unit u ON ( d.divid = u.divid ) JOIN location l ON ( l.unitid = u.unitid ) JOIN CRIMINAL c ON ( c.locid = l.locid ) GROUP BY divname