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

how to resolve -811

2 Answers   IBM,

cursor with hold explain ?

1 Answers   Verizon,

What is the COBOL picture clause of the following DB2 data types: DATE, TIME, TIMESTAMP?

3 Answers  

What error I will get if my select query gives multiple row while executing a Cobol-DB2 program.How can I solve it without using a cursor.

4 Answers   Cap Gemini, Verizon,

How to fetch the last row from the table in SQL (db2)?

0 Answers  

database DSNDB01 means a.db2 catalog b.db2 directory c.BSDS d.active logs answer with reason please

1 Answers  

How do u achieve record level locking in DB2 versions when record level locking is not allowed?

2 Answers   IBM,

How to rename a table in DB2 ?

0 Answers   MCN Solutions,

Explain the use of the WHERE clause.

2 Answers   IBM,

How do you define a correlated name?

1 Answers  

What is meant by the attachment facility?

3 Answers  

What is a db2 collection?

0 Answers