|
View previous topic :: View next topic
|
| Author |
Message |
Cketha
Warnings : 1
New User
Joined: 19 May 2007
Posts: 22
Location: mumbai
|
|
|
|
Hi,
I have field in DB2 table defined with Date as a data type, it's having daily details like
2007-29-01
2007-20-01
2007-30-01,
2007-31-02
2007-31-02
in CCYY-DD-MM format
Is it possible to group by year & month without considering the DD
We want only one record per month.
Can you help regarding this? |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Cketha wrote: |
Hi,
I have field in DB2 table defined with Date as a data type, it's having daily details like
2007-29-01
2007-20-01
2007-30-01,
2007-31-02
2007-31-02
in CCYY-DD-MM format
Is it possible to group by year & month without considering the DD
We want only one record per month.
Can you help regarding this? |
You can do it with
| Code: |
SELECT YEAR_MONTH, SUM(AMOUNT) FROM
(SELECT SUBSTR(CHAR(DATE_COLUMN,ISO),1,7) AS YEAR_MONTH, AMOUNT FROM TABLE)
GROUP BY YEAR_MONTH
|
|
|
| Back to top |
|
|
Cketha
Warnings : 1
New User
Joined: 19 May 2007
Posts: 22
Location: mumbai
|
|
|
|
| Craq Giegerich wrote: |
| Cketha wrote: |
Hi,
I have field in DB2 table defined with Date as a data type, it's having daily details like
2007-29-01
2007-20-01
2007-30-01,
2007-31-02
2007-31-02
in CCYY-DD-MM format
Is it possible to group by year & month without considering the DD
We want only one record per month.
Can you help regarding this? |
You can do it with
| Code: |
SELECT YEAR_MONTH, SUM(AMOUNT) FROM
(SELECT SUBSTR(CHAR(DATE_COLUMN,ISO),1,7) AS YEAR_MONTH, AMOUNT FROM TABLE)
GROUP BY YEAR_MONTH
|
|
Thank you.
It will work. |
|
| Back to top |
|
|
Abirami.YN
New User
Joined: 16 Jul 2005
Posts: 15
Location: India - Chennai
|
|
|
|
Hi
I am getting error for the above query. Error Message is
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<EMPTY>". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: CORRELATION NAME
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM10 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 471 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'000001D7' X'00000000' SQL DIAGNOSTIC INFORMATION
My Query:
SELECT YY1MM, SUM(PURCHASE_AMT)
FROM (SELECT SUBSTR(CHAR(ARCH_DATE,ISO),1,7) AS YY1MM,
PURCHASE_AMT FROM PURTBL)
GROUP BY YY1MM;
Could you plz help me.
Thank You. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Quote: |
SELECT YY1MM, SUM(PURCHASE_AMT)
FROM (SELECT SUBSTR(CHAR(ARCH_DATE,ISO),1,7) AS YY1MM,
PURCHASE_AMT FROM PURTBL)
GROUP BY YY1MM;
|
try
| Code: |
SELECT YY1MM, SUM(PURCHASE_AMT)
FROM (SELECT SUBSTR(CHAR(ARCH_DATE,ISO),1,7) AS YY1MM,
PURCHASE_AMT FROM PURTBL) x
GROUP BY YY1MM;
|
|
|
| Back to top |
|
|
|
|
