[2005] sql question

[.paul.]

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?

[jmcilhinney]

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.

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

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

[.paul.]

it would be something more like

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

[penagate]

What's an "area"?

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

[penagate]

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.

[.paul.]

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

[.paul.]

i've been thinking about this

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

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

[penagate]

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.

[.paul.]

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

[jmcilhinney]

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:

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:

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

[penagate]

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

Non-relational databases are the most complex to manage.