It should work this way also... SELECT X.YEAR, COUNT(X.SNAME) FROM ( S

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 / raj

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

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

Is This Answer Correct ?

0 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Mention a credible reason why select* is never given preference in an sql program that has been embedded.

1249

can any one provide me the link for the db2 v7 & db2 v8 manual for Z/os? i need to know about the syntax of REORG in both versions & need to know the difference as well

2299

What's The Error Code For Unique Index Voilation?

1310

What is table space in db2?

1072

What is the role of union all and union

1195

How to resolve deadlock issue

20334

How would the varchar column remarks defined?

1136

Mention the length of physical storage of the given data types of db2 – date, timestamp, time

1144

What is a db2 package?

1131

What does db2 stand for?

1095

What is the result of open cursor statement?

1129

Explain what are the various isolation levels possible?

1101

In an sql table that is embedded, what is the procedure to retrieve rows that are part of a db2 table?

1154

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

11746

What are the prerogatives?

1133