| View previous topic :: View next topic |
| Author |
Message |
Celeste Locklin
Beginner
Joined: 04 May 2006
Posts: 26
Topics: 10
Location: USA
|
 Posted: Thu Jun 05, 2008 6:48 pm Post subject: count of rows by month using a Timestamp field |
 |
|
Is there an "easy" way to count the number of rows per month in a timestamp field on a DB2 table?
I want to count how many rows posted in jan, how many in feb, mar, etc.
for a given year. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Jun 05, 2008 7:59 pm Post subject: |
|
|
Celeste Locklin,
Try this untested sql which will you the desired results
| Code: |
SELECT A.MNTH_NAME
,COUNT(*)
FROM (SELECT MONTH(your timestamp col) AS MNTH_NAME
FROM TABLE
WHERE YEAR(Your timestamp col) = 2008) A
GROUP BY A.MNTH_NAME
; |
Hope this helps...
Cheers
Kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Fri Jun 06, 2008 1:22 pm Post subject: |
|
|
How about this.
| Code: |
Select Month(Timestamp), Count(*)
from table
where Year(timestamp) = 2008
group by Month(Timestamp) |
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri Jun 06, 2008 1:25 pm Post subject: |
|
|
NASCAR9,
I believe you canNOT do it that as the Group by clause needs Existing column name in the table.
Kolusu |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Fri Jun 06, 2008 2:31 pm Post subject: |
|
|
| Code: |
SELECT MONTH_TIMESTAMP, COUNT(*)
FROM (SELECT MONTH(TIMESTAMP) AS MONTH_TIMESTAMP
FROM TABLE WHERE YEAR(TIMESTAMP) = 2008) B
GROUP BY MONTH_TIMESTAMP;
|
|
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Fri Jun 06, 2008 2:47 pm Post subject: |
|
|
| kolusu wrote: | NASCAR9,
I believe you canNOT do it that as the Group by clause needs Existing column name in the table.
Kolusu |
I just ran this
| Code: | Month(lastupdt) as MM, Count(*) as total
FROM "HOURS".PREMCHG
where Year(lastupdt) = 2004
group by Month(lastupdt)) |
and here are the results
MM total
11 37
10 83
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Fri Jun 06, 2008 3:01 pm Post subject: |
|
|
| kolusu wrote: | NASCAR9,
Is this on z/OS?  |
Yes, V8 DB2, not sure ov the zOS version..
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Fri Jun 06, 2008 3:10 pm Post subject: |
|
|
The zOS is 1.8
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Fri Jun 06, 2008 3:14 pm Post subject: |
|
|
| Code: |
SELECT MONTH_TIMESTAMP, COUNT(*)
FROM (SELECT MONTH(TIMESTAMP) AS MONTH_TIMESTAMP
FROM TABLE
WHERE YEAR(TIMESTAMP) = 2008) B
GROUP BY MONTH_TIMESTAMP;
|
Works fine for me with ZOS DB v7. The group by has an existing column name it is created in the inner select. Instead of saying it won't work prove it. |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Fri Jun 06, 2008 3:24 pm Post subject: |
|
|
| CraigG wrote: | | Code: |
SELECT MONTH_TIMESTAMP, COUNT(*)
FROM (SELECT MONTH(TIMESTAMP) AS MONTH_TIMESTAMP
FROM TABLE
WHERE YEAR(TIMESTAMP) = 2008) B
GROUP BY MONTH_TIMESTAMP;
|
Works fine for me with ZOS DB v7. The group by has an existing column name it is created in the inner select. Instead of saying it won't work prove it. |
CraigG, Was this directed at me? I don't think any of the posts above were negative in anyway. I pointed out another way to get the results. In SQL usually it can be accomplished more than one way.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
Celeste Locklin
Beginner
Joined: 04 May 2006
Posts: 26
Topics: 10
Location: USA
|
| Posted: Thu Jul 24, 2008 7:01 pm Post subject: |
|
|
| excellent ! thanks to all !! |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Fri Jul 25, 2008 11:58 am Post subject: |
|
|
If you want to select multiple years, try this:
| Code: | SELECT CCYY_MM, COUNT(*)
FROM ( SELECT SUBSTR(CHAR(<timestamp_colnm>),1,7) AS CCYY_MM
FROM <table_nm>
WHERE <predicates> ) as temp_tbl
GROUP BY CCYY_MM
ORDER BY 1
for fetch only with ur
CCYY_MM 2
------- -----------
1980-01 1
2003-08 1
2004-11 2
2006-10 1
2007-04 1
2008-06 1
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
