AW: SQL summary qry

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

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
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, 
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.