| Help:how to create the special view below? [message #371714] |
Wed, 29 November 2000 06:39  |
jiang king
Messages: 1
Registered: November 2000
|
Junior Member |
|
|
Eexcuse me,my english is poor!
There is a table,named emp:
emp_no number,
dept_no varchar2(4),
name varchar2(30),
sexy varchar2(6)
emp table have a record only:
emp_no dept_no name sexy
--------------------------
1 0005 mike male
now create a view to get how many man and woman for every department:
create view sexy_count(
dept_no,sexy,vcount)
as select dept_no,sexy,count(*)
from emp
group by dept_no,sexy
Excute the following SQL state:
select * from sexy_count;
result is£º
dept_no sexy vcount
--------------------------
0005 male 1
but I want get the result :
dept_no sexy vcount
--------------------------
0005 male 1
0005 female 0
how to create the view?
|
|
|
|
| Re: Help:how to create the special view below? [message #371729 is a reply to message #371714] |
Thu, 30 November 2000 06:02  |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Jiang,
Try this. I guess it might be done better, but this will work for now
CREATE VIEW SEXY_COUNT AS
SELECT DEPT_NO, SEXY, SUM(VCOUNT) FROM
(
SELECT DEPT_NO, SEXY, COUNT(1) VCOUNT
FROM EMP
GROUP BY DEPT_NO, SEXY
UNION ALL
SELECT DISTINCT DEPT_NO, 'male', 0 VCOUNT
FROM EMP
UNION ALL
SELECT DISTINCT DEPT_NO, 'female', 0 VCOUNT
FROM EMP)
GROUP BY DEPTNO, SEXY
hth
Prem :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
