Member Avatar for g00se 
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.
  • 2 Contributors
  • 4 Replies
  • 131 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by g00se
Member Avatar for pritaeas

All the queries I fired gave inaccurate count values.

Show your queries.

Member Avatar for 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 because: Code tags
Member Avatar for pritaeas

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
Member Avatar for g00se

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 !

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.