Re: SQL summary qry

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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.