By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

Difficoult Query ... or maybe too much easy ...

P: n/a
Let us suppose we have this table

ID - CITIES
1 New York
2 New York
3 Los Angeles
4 Los Angeles
5 Los Angeles
6 Atlanta
7 Atlanta
8 Atlanta
9 Atlanta

How can I obtain the following ouput with a select query?

Atlanta (4)
Los Angeles (3)
New York (2)
Dec 31 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
LaSalamandra wrote:
Let us suppose we have this table

ID - CITIES
1 New York
2 New York
3 Los Angeles
4 Los Angeles
5 Los Angeles
6 Atlanta
7 Atlanta
8 Atlanta
9 Atlanta

How can I obtain the following ouput with a select query?

Atlanta (4)
Los Angeles (3)
New York (2)
FredG gave you a sample command line. If you want to explore the
QueryBuilder, you could, from the DataWindow, select
Query/New/DesignView (select your table) then Add then press Close.

From the table in the upper window drag down the field with the city
name twice...the city field should be in 2 columns. From your menubar
select View/Totals. The first column should be GroupBy in the "Total"
row and in the second select from the dropdown "Count". Now from the
menubar select Query/Run.

Ibiza
http://www.youtube.com/watch?v=-YfG3tfB8F0

Dec 31 '07 #2

P: n/a
On Sun, 30 Dec 2007 17:41:50 -0800 (PST), LaSalamandra <gs*********@gmail.com>
wrote:
>Let us suppose we have this table

ID - CITIES
1 New York
2 New York
3 Los Angeles
4 Los Angeles
5 Los Angeles
6 Atlanta
7 Atlanta
8 Atlanta
9 Atlanta

How can I obtain the following ouput with a select query?

Atlanta (4)
Los Angeles (3)
New York (2)
Change the name of the table as required -

SELECT Count(ID) AS CountOfCities, Cities
FROM tblCities
GROUP BY Cities;
Wayne Gillespie
Gosford NSW Australia
Dec 31 '07 #3

P: n/a
Very very kind of you. Thank you and happy new year.

Dec 31 '07 #4

P: n/a
On Dec 31 2007, 6:10*am, lyle <lyle.fairfi...@gmail.comwrote:
>
From a Microsoft article of many years ago:

"Use Count(*) instead of Count([ColumnName]). Jet has built-in
optimizations that make Count(*) much faster than column-based
counts."

As I understand it, at that time Jet counted in any available index
for Count(*), but it scanned the table for Count([ColumnName]).

Is this still pertinent for ACE, T-SQL and JET 4.0? I'm not sure, but
my guess is "Yes".
From Jet Database Engine Programmer's Guide (Jet 3.5), Second Edition,
ISBN 1-57231-342-0, p. 159 (Great book Lyle; thanks for bringing it to
my attention.):

Common Pitfalls

Using Count(<fieldname>) instead of Count(*). When you have to
determine the number of records, you should use Count(*) rather than
Count(<fieldname>) because there are Rushmore optimizaitons that allow
Count(*) to be executed much more quickly than Count(<fieldname>).

Since the argument is that the benefit is due to Rushmore Technology
on the indexes, my guess is "yes" also. By performing the
intersection or union operations on the indexes rather than on the
data, Rushmore Technology benefits greatly from multiple indexes used
in JOIN's, used for criteria or used for sorting -- to a point.

op cit:

This makes indexing many fields advantageous. However, keep in mind
that adding indexes to fields can cause performance to suffer.

I assume they're referring to the performance problems presented in
the A97 Help File (Optimize general table performance):

Indexes aren't appropriate in all cases, however. Indexes add to the
size of the .mdb file, reduce concurrency (the ability of more than
one user to modify a page at the same time) in multiuser applications,
and decrease performance when you update data in fields that are
indexed, or when you add or delete records. It's a good idea to
experiment to determine which fields should be indexed. Adding an
index may speed up a query one second, but slow down adding a row of
data by two seconds and cause locking problems. Or it may add
negligible gains depending on which other fields are indexed. For
example, adding an index to a PostalCode field may provide very little
performance gain if a CompanyName field and LastName field in the
table are already indexed. Regardless of the types of queries you
create, you should only index fields that have mostly unique values.

Hmm..., maybe by using less indexes I can use more bound forms since I
won't get as much locking :-). I don't think I'll even try that!

James A. Fortune
CD********@FortuneJames.com
Jan 4 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.