Answer / amita sharma

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

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!!!

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)

Answer / m4io

select A.yr, count(*) from
(select year(doj) from emp_tbl) A
group by a.yr

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.

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;

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...

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

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

What is check constraint in db2?

0 Answers  

What information is used as input to the bind process?

2 Answers  

What is the advantage in De-normalizing tables in DB2?

0 Answers  

What is the use of dclgen in db2?

0 Answers  

What is an intent lock?

3 Answers  

What is the difference between group by and order by?

2 Answers   IBM, TCS,

Define db2 and its databases?

0 Answers  

how can u nderstand the sql stmts executed successfully or not ?

1 Answers   TCS,

when we are tying to update a table having 100 rows. if the program abends when updating 51 row . how to start updating again from the 51 row .what was the logic

2 Answers   TCS,

what is contained in the DB2 node lock file? A) data base names B) data base users names C) license keys D) server names

5 Answers   Accenture,

I have some 3 particular fields ..i want to know which all tables have those 3 fields. Is there any way to identify.. can we know by quering system tables..

5 Answers   CTS,

Name the various locking levels available?

0 Answers