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
- 11254 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 |
How connect db2 database to datastage?
how to get duplicate records from tables?write qurey for this?
List out the data types available.
What is database reorganization?
Discuss about db2 bind?
Is it possible to create an Instance in DB2 using DB2 Control Center ?
How do I optimize a query in db2?
Where could you look if you had a question about whether a column has been defined as an index?
What is cascading rollback?
how will i display the duplicate record's from a table... i don't want to eliminate ...i want to display the duplicate record...for example in my table i have 10 record's like record no(1,2,3,4,2,9,6,1) in this ...so i want to receive duplicates...
How to check last update on table in db2?
What is QUIESCE?
COBOL 
