Answer / amita sharma

Hi,

You can write the query as below:

select year(doj) as year, count(*) as count from emp_join
group by year(doj);


Let: table name is: emp_join

For furthur clarifications or queries please ask.

Thanks;
Amita Sharma.

Output:

year count

2004 1
2007 3
2008 1
2009 2

Answer / vish

One way of getting the desired result is using the
VIEWS...try the given below...

CREATE VIEW EMP_YEAR
SELECT SUBSTR(DOJ(1,4) AS YOJ, SNAME FROM EMP_TBL

now select the data using this view...

SELECT YOJ, COUNT(*) FROM EMP_YEAR GROUP BY YOJ..

Note: check for the actual syntax of creating a view..what
I mentioned here is just way of donig it and not the actual
syntax.

Do let me know the output once you try it out...it should
work...enjoy!!!

Answer / saeed

Hi Rama Krishna Reddy,

What you tried in db2 and oracle i dont know but year() can
be used in DB2 ver 9.5, and the result of above query
posted by Amita Sharma might not be correct but which
functions she used is correct .

As per my opinion following qry can give desired result.


SELECT DISTINCT YEAR(DOJ) AS YEAR , COUNT(*) AS COUNT FROM
TABNAME GROUP BY YEAR(DOJ)

Answer / m4io

select A.yr, count(*) from
(select year(doj) from emp_tbl) A
group by a.yr

Answer / sambit mohapatra

select distinct substr(DOJ,(1,4)),count(*) from emp_tbl
group by DOJ

I hope that this quey is work fine and let me know the
result.

Answer / raj

It should work this way also...

SELECT X.YEAR, COUNT(X.SNAME)
FROM (
SELECT SUBSTR(DOJ,1,4) AS YEAR, SNAME
FROM EMP_TBL
) X
GROUP BY X.YEAR;

Answer / rama krishna reddy

Hi Amita,

i already tried with the query that you mentioned..
it will work in oracle...but it will not work in db2..
db2 doesn't allow any function like year(),month(),substr
()...in group by clause...

Answer / rama krishna reddy

Hi harish,

the query you mentioned will give diffrent result like

col1 count
---------- --------
2007-03-19 1
2007-05-19 2
2008-05-19 1
2009-05-19 2
2004-05-19 1

but the number of students joined in 2007 is 3 but is
showing in 2 diff lines in the output for ur query

Answer / harish

Hi,

I hope this query will work ...

u didnt understand the REK,they gave only output format
column names cannot be changed.....

select substr(DOJ,(1,4)),count(*) from emp_tbl
group by DOJ

let me know if i am wrong


HARISH POOMGAME SHIVAPPA
NIIT TECH
KOLKATA

What are the various isolation levels possible?

0 Answers  

Hi Team, Please tell me, If i don't declare the cursor in the program and i have given open and fetch and close, then what error will get.

6 Answers   IBM,

List out the data types available.

0 Answers  

What is the usage of open cursor command?

0 Answers  

List down the data types in the db2 database.

0 Answers  

what is commit & rollback? When and where is used while compiling a cobol-db2 program?

2 Answers  

What is DYNSLT keyword? How do you perform selection using DYNSLT

1 Answers  

In a Cobol-DB2 program, I am fetching rows from 4 tables using cursor and then based on the a field present in that table, It processes the information accordingly..for example stat-c is one digit field..if stat-c is 'D' then the a row is deleted from table and written those details in to a file. If the stat-c is 'U' then a row is updated (hardcoded what to update)in a table and written those details in to a file. If the stat-c is 'I' then a row is inserted in a table and written those details in to two files. The issue is i have to include the intermediate commits. When an abend occurs, due to commit statement db2 tables will be saved, But there will be lose of file contents. When we resubmitting the job associated with this program there will be insert ,update and delete anomolies to avoid that what measures could be taken?. The intermediate commit is nothing but issuing commit after massive inserts, updates and deletes(sum of 500actions)

2 Answers  

what is the maximum number of tables that can be joined ?

8 Answers   IBM, TCS,

i have a db2 variable how do u declare tht into working storage section ?

1 Answers   TCS,

Hi, i would like to start a new carrier in DB2 DBA.Which and what all step should i have to take..?

0 Answers  

What is a Foreign Key?

0 Answers   Tavant Technologies, Zensar,