[2005] sql question

i've got a database with 12 tables, with about 1200 items in each table.
there are about 300 distinct "areas" in the database.

i can get a list of these distinct areas using the distinct keyword.

how can i get a list of the number of times each of these distinct areas are listed without querying each distinct area?

SQL questions belong in the Database Development forum, not VB.NET. Just because this query will be executed from a VB.NET application doesn't make it a VB.NET question.

Code:

---

SELECT Name, COUNT(Name) FROM Area GROUP BY Name

---

I'm no database guru but I'm pretty sure that should work.

it would be something more like

Code:

---

select area, count(area) from month1 group by area

---

the problem is theres 1200 records in each table, with approx 300 different areas. i don't want to have to iterate through 300*12 tables. i'll give it a try

What's an "area"?

You'll have to be more specific about your table schema.

Quote:

---

Originally Posted by .paul.

the problem is theres 1200 records in each table, with approx 300 different areas. i don't want to have to iterate through 300*12 tables.

---

You're not. The beauty of SQL is that it's declarative. You don't have to care how it works, it just does.

In any case, 1200 records is nothing, to any half-capable DBMS at least.

an "area" is 1 of the columns (fields) in the table.
there are 12 tables in the database

i've been thinking about this

Code:

---

SELECT Name, COUNT(Name) FROM Area GROUP BY Name

---

wouldn't i have to do 300 querys per table?

If you ever have to do more than one or two queries to extract the required data something's seriously wrong with your table structure.

Run the query that J provided and see if that works. If it doesn't, describe your table structure in detail, preferably with a diagram and example record, and example of the result you want from the query.

its a very simple database. its not relational, it has no links.
the 300 distinct areas are towns.
out of the 1200 records in a table, i need to know how many of each town there are. i'm not sure how to use that sql statement

How do you use any SQL query? You execute it and get the result set either via a DataReader or use a DataAdapter to populate a DataTable. Using my original query you'd get two columns: one containing the names and the other containing the number of times that name appears in the table.

Having said that, I'm a bit concerned by this:

Code:

---

select area, count(area) from month1 group by area

---

You don't have a different table for each month do you?

Also, you should give the second column a name, so I'd change my query from before to something like this:

Code:

---

SELECT Name, COUNT(Name) AS [Count] FROM Area GROUP BY Name

---

Quote:

---

Originally Posted by .paul.

its a very simple database. its not relational, it has no links.

---

:eek2:

Non-relational databases are the most complex to manage.