× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Danke sehr, Birgitta, and thanks Vern.

My german is very rusty, as I have not been there since 1976, and have not
kept up.

In retyping my example, I put the ascending in the wrong place - Sigh! And
thanks for the spell check, Vern. ;^)

Ordering is not the problem, but that the I am not getting the results that
I am looking for. I will try again when I get to work tomorrow, but I don't
see any difference between Vern's and my example, except naming the fields
in the group by clause.


Jim

On 7/5/07, BirgittaHauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Hi,

As Vern already pointed out ascending or descending sequences cannot be
specified in the group by clause, it is only allowed for the order by
clause.
In either way ASC is the default and is therefore optional.

A column name can be specified with all aggreat functions, also with
count().
In effect there is a difference between using an asteric or using a column
name.
When specifying a column name in an aggregat functions rows that contain
NULL values will be ignored.
When specifying an asteric all rows are considered.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Vernon Hamberg
Gesendet: Friday, July 06, 2007 05:39
An: Midrange Systems Technical Discussion
Betreff: Re: SQL summary qry


Jim

I'm pretty sure you can't use numbers and ascending or descending in
the group by - asc and desc don't make sense there anyway, because
grouping is not about ordering. Grouping happens before the result
set is generated, so you can't use the alias column names in the
group by clause. Sorting happens after the result set is built, so
you have to use numbers or the alias names. count() uses only the
asterisk - maybe column names can be used but I've never seen that it
matters.

select number_field,
year(date_field) as year_field,
month(date_field) as month_field,
count(*) as num_occurrences,
sum(amount_field') as total_amt
from somelib/somefile
group by number_field, year(date_field), month(date_field)
order by 1, 2, 3

might do it. Oh, I'm also a spelling freak, but that change doesn't
matter - LOL.

If that statement does no do it directly, you can use the "with ...
as ... " kind of thing to generate a table expression with field
names and the date broken up nicely.

HTH
Vern

At 06:59 PM 7/5/2007, you wrote:

>Listers,
>
>Ok, I have beat my head against the wall enough on this, time to
>consult the experts. It should be easy, but ..
>
>I also thought this was covered before and in the archives, but have
>not yet located that thread.
>
>I have the following fields; a date, an amount and a number. What I
>want to get is a list that has a count and total in each month and year
>by the number, kind of like this;
>
>Number Year Month Count Total amount
>001 1999 11 05 99.99
>001 1999 12 02 999.99
>002 1999 11 01 200.01
>002 2001 06 55 1234.56
>.
>.
>.
>Etc.
>
>I have the following code, but I know it is wrong, as it produces only
>totals per number, and not per year and month within number;
>
>SELECT Number_Field,
> Year(Date_Field) AS Year_Field,
> Month(Date_Field) AS Month_Field,
> count(Number_Field) AS Num_Occurances,
> Sum(Amount_Field') AS Total_Amt
> from SomeLib/SomeFile
> group by 1 asc, 2 asc, 3 asc
> order by 1, 2, 3
>
>Can anyone see what I am doing wrong? Give me a little guidance? Am I
>going to have to use a qmqry and a form to get the breaks by number -
>year- month that I want?
>
>Thanks!
>
>Jim Essinger
>Senior Programmer/Analyst
>SQL novice, but learning!
>
>208-452-9475


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.