Please help me in the following question:

Given the following three tables
Table 1: 
specialization (name,department)

Table 2:
area  (a_name,phone,address)

Table 3:
dep_loc (area_name,department)

where the "area_name" attibute in dep_loc references "a_name" in area.
Write a query to give a count of the number of people that work in each area
So the result should look something like this

**area name** | **count**
    new york  |   1
    washington|   1

also, if were to insert a new row into table area , what modification should i make to the above query so that the new row is accounted for?
All the queries I fired gave inaccurate count values. Not sure where I'm going wrong.
5 Years
Discussion Span
Last Post by g00se

i don't remember what queries i tried yesterday...it was something like this.....
i know the nested select statement makes sense, but i just can't figure out what the statement should be

select s.location, count(t.name) from sandwiches as s, tastes as t where t.name in(select name from tastes group by filling) group by location

Edited by pritaeas: Code tags


Try something like

SELECT dl.*, COUNT(*) AS cnt
FROM dep_loc dl, area a 
WHERE dl.area_name = a.a_name 
GROUP BY dl.area_name

and then extend it to what you need.

Edited by pritaeas


yeah this query gave me the correct result...thanks!...and i just realised...my previous post had location,sandiwches,tastes and fillings where they should have been area_name, area, specialization and department respectively, according to the tables I had provided. I was trying out queries on another set of tables and accidentally posted that query....sorry about that !

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.