View previous topic :: View next topic
|
Author |
Message |
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi,
I am having fields(eff-date) with the data type 'Date' in the DB2 table, I need to group by this field only on the basis of year and month(With out considering the day(d)
. Can you send the query for this
Input
Eff-date (mm/dd/ccyy) amount
01/31/2007 111
01/30/2007 222
01/29/2007 333
02/28/2007 333
02/27/2007 222
02/26/2007 222
Output should be
Eff-date(mm/dd/ccyy) amount
01/01/2007 666
02/01/2007 777
Thanks, |
|
Back to top |
|
|
Abirami.YN
New User
Joined: 16 Jul 2005 Posts: 15 Location: India - Chennai
|
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
We tired it , but it is not working. That query is giving error.
Can you suggest some other way
Thanks |
|
Back to top |
|
|
Abirami.YN
New User
Joined: 16 Jul 2005 Posts: 15 Location: India - Chennai
|
|
|
|
Try this below query
SELECT YY1MM, SUM(PURCHASE_AMT)
FROM (SELECT SUBSTR(CHAR(ARCH_DATE,ISO),1,7) AS YY1MM,
PURCHASE_AMT FROM PURTBL) x
GROUP BY YY1MM;
It's working for me. Let me know what error message or what result you are getting??
Regards,
Abirami. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello Bala,
You will usually gets better replies sooner if when you post something is not working, you also post any diagnostic information available from the failed process.
Good luck |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi
I have tried the below query
SELECT YY1MM, SUM(BID_RT), FK_CURRID
FROM (SELECT SUBSTR(CHAR(CREATION_DT,ISO),1,7) AS YY1MM, FK_CURRID,
BID_RT FROM EXCG) X
GROUP BY YY1MM;
and i got the below error msg
QUERY MESSAGES:
The grouping is inconsistent.
In the table datas are stored like
FK_CURRID CREATION_DT BID_RT
------------- ---------------- ---------
006 05/31/2007 1.06860000
006 05/30/2007 1.07100000
006 05/29/2007 0.00000000
Please help me to solve the error in the query
Thanks |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
banand wrote: |
Hi
I have tried the below query
SELECT YY1MM, SUM(BID_RT), FK_CURRID
FROM (SELECT SUBSTR(CHAR(CREATION_DT,ISO),1,7) AS YY1MM, FK_CURRID,
BID_RT FROM EXCG) X
GROUP BY YY1MM;
and i got the below error msg
QUERY MESSAGES:
The grouping is inconsistent.
In the table datas are stored like
FK_CURRID CREATION_DT BID_RT
------------- ---------------- ---------
006 05/31/2007 1.06860000
006 05/30/2007 1.07100000
006 05/29/2007 0.00000000
Please help me to solve the error in the query
Thanks |
If you would read about grouping you would find that you need
GROUP BY YY1MM, FK_CURRID |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi
It is working fine, after adding the FK_CURRID field in the Group By Clause.
Thanks a lot
Can I know why we need to add that column in the Query?.
Is it like, other than the column which ever used for the aggregate function should be mention in Group By clause.
Thanks, |
|
Back to top |
|
|
|