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

db2 query

I have one table with the following details.

SNO SNAME DOJ
------ -------------------- ----------
10 KRISH 2007-03-19
20 REDDY 2007-05-19
30 RRRRR 2007-05-19
40 BBBBB 2008-05-19
50 CCCCC 2009-05-19
60 JJJJJ 2009-05-19
70 JJJJJ 2004-05-19
i want the output in the following format:( no of students
joined in each year(no nedd to consider about month and
date)

year count
--------- ----------
2004 1
2007 3
2008 1
2009 2

Question Posted / vish

9 Answers
14250 Views
TCS, I also Faced
E-Mail Answers

Answer Posted / 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!!!

Is This Answer Correct ?

3 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Can one database have multiple instances?

1048

What is temporal table in db2?

1034

How to view db2 table structure?

1012

What is the difference between oracle and db2?

1097

can all users have the privilage to use the sql statement select * (dml)?

1163

What is the difference between bind and rebind in db2?

1446

What is meant by union and union all?

1006

What is dbrm? What it contains?

1161

How to rename a table in DB2 ?

1086

Is db2 relational database?

971

SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CHAR, TOKEN KEY was expected

11673

Explain about dbrm.

1038

Which isolation level provides highest data integrity?

1114

How to check last update on table in db2?

1053

What is db2 and what is the use of db2 optimizer?

1055