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

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

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

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)

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

On Sun, 30 Dec 2007 17:41:50 -0800 (PST), 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)

SELECT YourTable.City Count(YourTable.City) AS CountOfCity
FROM YourTable
GROUP BY YourTable.City
ORDER BY YourTable.City;
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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

On Sun, 30 Dec 2007 17:41:50 -0800 (PST), 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)

Change the name of the table as required -

SELECT Count(ID) AS CountOfCities, Cities
FROM tblCities
GROUP BY Cities;


Wayne Gillespie
Gosford NSW Australia

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

Very very kind of you. Thank you and happy new year.

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

On Dec 30, 9:17 pm, fredg wrote:
> On Sun, 30 Dec 2007 17:41:50 -0800 (PST), 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)
>
> SELECT YourTable.City Count(YourTable.City) AS CountOfCity
> FROM YourTable
> GROUP BY YourTable.City
> ORDER BY YourTable.City;
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

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".

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

Uhmm ... may I ask another question?

I would like to make a UNION of queries from two different
databases ...

Let us suppose we have Database USA and Database EUROPE. Both have the
same exact table "cities".

Is it possible to make a union of following two queries:

QUERY ON DB USA

SELECT Count(ID) AS citicount, city
FROM cities
GROUP BY city
ORDER BY citicount desc

QUERY ON DB EUROPE

SELECT Count(ID) AS citicount, city
FROM cities
GROUP BY city
ORDER BY citicount desc

?

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

On Dec 31 2007, 6:10=A0am, lyle wrote:
>
> 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".

=46rom 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() instead of Count(*). When you have to
determine the number of records, you should use Count(*) rather than
Count() because there are Rushmore optimizaitons that allow
Count(*) to be executed much more quickly than Count().

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
CDMAPoster@FortuneJames.com