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 is deadlock in db2?

0 Answers  

What happens in bind step in a db2 program?

0 Answers  

Under what circumstances will DB2 allow an SQL statement to update more than one primary key value at a time?

1 Answers  

If we keep the DCLGEN structure for a table in a copybook and include it in the COBOL program using the COPY statement, will there be any impact during compilation or at any stage of program execution?

2 Answers  

how many bytes timestamp token occupies?

6 Answers   Boss Global, Cap Gemini, TCS,

In cobol+DB2program if the code of cobol program has changed than i have to do precompilation once again?

6 Answers   TCS,

What is the picture clause of the null indicator variable?

6 Answers  

When is the authorization check on DB2 objects done - at BIND time or run time?

1 Answers  

For Null indicator we have 3 values 0,-1,-2. 0->Not nulll -1->Null -2->Value truncated If ther is any possibility to have any positive value other than 0 in Null indicator.If it is so then when we will get and what is the value?

1 Answers   BNP Paribas,

What do you mean by cursor?

0 Answers  

In which column of which DB2 catalog would you find the length of the rows for all tables?

2 Answers  

How to check last update on table in db2?

0 Answers