Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 13943 Views
- TCS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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
| Is This Answer Correct ? | 4 Yes | 1 No |
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!!!
| Is This Answer Correct ? | 3 Yes | 0 No |
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)
| Is This Answer Correct ? | 3 Yes | 1 No |
Answer / m4io
select A.yr, count(*) from
(select year(doj) from emp_tbl) A
group by a.yr
| Is This Answer Correct ? | 0 Yes | 0 No |
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.
| Is This Answer Correct ? | 0 Yes | 0 No |
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;
| Is This Answer Correct ? | 0 Yes | 0 No |
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...
| Is This Answer Correct ? | 1 Yes | 2 No |
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
| Is This Answer Correct ? | 0 Yes | 1 No |
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
| Is This Answer Correct ? | 0 Yes | 2 No |
What is the difference between CS and RR isolation levels?
How does DB2 determine what lock-size to use?
Bind concepts in DB2 cobol
10 Answers IBM, TCS, TRD, Virtusa,
How can you count the number of rows from a table tab?
What is the difference between using bind () and rebind () methods of naming class?
What is a lock?
What is a page in db2?
Give a brief description of db2 isolation levels?
Is Cursor exicutable ?
What is auditing?
Q4. How will you get the count of record without using COUNT verb in query?
Can we declare DB2 HOST variable in COBOL COPY book?
COBOL 
