SQL query help ..

[Guest_imported]

I have a Table with a column defined as date on DB2 V5 mainframe. lets say the column INVDATE is defined as a date field.

I would like to know the total number of rows for each year.

is there a way that i can design a query to get this information.

i can get a list of years by using
select distinct Year(INVDATE) from test1.invoice.

this doesnt work

select Count(invdate) from test1.invoice
group by Year(invdate)

Thanks....

Dennis

 

[MarcLodge]

Dennis,
I'm at home rather than at work with access to a mainframe so don't have a chance to check out what I'm about to tell you. Please forgive if this is incorrect, but...... I think you're almost there with your last example. I think you want:

SELECT COUNT(*), YEAR(INVDATE)
FROM TEST1.INVOICE
GROUP BY YEAR(INVDATE)

You may not need to group by YEAR(INVDATE) but just by INVDATE as I can't remember whether the grouping requires the function or not.

Hope this helps/works! Once again apologies if not.

Marc

 

[yarnseeker]

Unfortunately, I don't think you can group by a field and use a function on that field. I've tried countless times myself! I've even tried using column numbers like

select year(invdate), count(*)
from test1.invoice
group by 1

That doesn't work either!

There are only a couple of ways that I know around this.
1) Do this 1 yr at a time
2) create another table that has the years in it and join and then code

select invyear, count(*)
from test1.invoice, test1.invoiceYR
where year(invdate) = invyear
group by invyear

If you have access to something like QMF where you are allowed to create tables "on the fly", then option #2 works great!

HTH!

 

[Hattusas]

If I understood the situation correctly,this can be a solution.
In order to explain better;I will write down all of the steps I performed.
1#
create table test.test1 (
cdate date,
id int)
2#
insert into test.test1 (cdate,id) values
('01/01/2002',1),
('01/02/2002',2),
('11/01/2002',3),
('10/01/2002',4),
('01/01/2004',5),
('01/01/2001',6)
3#
select year(cdate) as rows,count(*) from test.test1 group by year(cdate) will return this result :

ROWS 2
----------- -----------
2001 1
2002 4
2004 1

3 record(s) selected.
Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[matchum]

din987,

Whats' wrong with good old:
select count(*),year(hiredate) from employee group by year(hiredate)