I want to display the department no. and the no. of employee in each department from EMP Table.(in a Single Row). Pls help me how to write the query for that.
I had one query which display the result in 3 rows.

select deptno, count(*) from emp
group by deptno;

Dptno Count(*)
10 5
20 3
30 4

I want to display this 3 rows in a single-row.
For Eg:

Dpt10 Count(*) Dpt20 Count(*) Dpt30 Count(*)
10 5 20 3 30 4

The output in this forum is not proper but try to understand that the no. 5,3 & 4 should be below count(*) column and 10,20 & 30 should be below deptno.

Recommended Answers

All 7 Replies

select '10 '||sum(a)||' 20 '||sum(b)||' 30 '||sum(c) "Dept vs count(*)" from(select decode(deptno,10,count(Sal)) a, decode(deptno,20,count(Sal)) b, decode(deptno,30,count(Sal)) c from emp group by deptno)

You are supposed tp post the code that you are working on before asing for code(solution)

Hi, you can used stragg function to get the output as ur requirement.

select sys.stragg(column_name||' ') from table_name
where rownum < 5

select wm_concat(deptno ||' ' || count(*)) from emp group by deptno;

hello i think this is to simple you write this code
tablename emply
columnname epname,deptno,age,sex
select distinct(deptno),epname from emply;

commented: zombie... -3

sorry your answer is
select departmentno,count(empname) as employee from emply group by departmentno;

ok it will work very well thanks Nitesh Srivastva 9718313245

commented: zombie +0

select array_agg(deptno||' '),array_agg(epname||' ') from emply where epid < 10

    this will work in a single row
commented: zombie +0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.