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
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 db2 isolation?
What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?
How can you find out the # of rows updated after an update statement?
If the main program has only cobol statements and subprogram has the db2 statements what is the procedure for precompilation. for which program we need to do the precompilation
How do you concatenate the firstname and lastname from emp table to give a complete name?
What is sqlca’s maximum length?
What is the maximum size of varchar data type in db2?
What is the error code -803 ?
In cursor program perform para varying until 1 by 1 fetch para cursor------close para in the blank what will come for confirming in the database?
which SQL comment successfully removes uncommitted changes from a DB2 database A) rollback B) delete C) drop D) decommit
What is RUNSTATS?
Is it possible using max on a char column?