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
- 9 Answers
- 11338 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
How to create db2 table in mainframe?
Is it possible to create an Instance in DB2 using DB2 Control Center ?
Where can you declare a cursor in a cobol-db2 program?
What does reorg do in db2?
How would you find out the total number of rows in a db2 table?
What is performance tuning db2?
What is ibm db2 connect?
What is a system catalog table in db2?
What is db2 instance?
What are the 2 sqlcodes that are returned?
How can tablespace be moved to another dasd volume that is allocated for that tablespace?
Why do we use cursor?
What is db2 bind process?
What action db2 takes when a program aborts in the middle of a transaction?
is it compulsory commitment control in journal?
