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.