Home » SQL & PL/SQL » SQL & PL/SQL » Count as array (oracle 9.2.0.1)
Count as array [message #362704] |
Wed, 03 December 2008 16:09  |
 |
deb.b
Messages: 44 Registered: December 2008
|
Member |
|
|
select count(*)
from all_users
where username in ( 'SYS','SYSTEM') group by username;
The output is
count(*)
________
1
1
select count(*)
from all_users
where username in ( 'SYS','XUSER','GRXU1','XXX01') group by username;
Now if XUSER,GRXU1,XXX01 is not in ALL_USERS table then
Count(*)
________
1
But I want to store it into an array and display like below.
count
________
1
0
0
0
Assuming that USERNAME contains duplicates if
select count(*)
from all_users
where username in ( 'SYS','XUSER','GRXU1','XXX01','ORA12') group by username;
count
________
1
0
0
0
3
ORA12 found 3 times in table
How can I do this.
[Updated on: Wed, 03 December 2008 16:35] Report message to a moderator
|
|
|
Re: Count as array [message #362712 is a reply to message #362704] |
Wed, 03 December 2008 20:01   |
wmgonzalbo
Messages: 98 Registered: November 2008
|
Member |
|
|
I'm just learning PL/SQL without any formal training yet, but let me try this one for practice purposes.. As I would also like to know how it should be done properly..
Tried this but it doesn't does the job properly:
select DECODE (v_count,0,'0',v_count) COUNT
from (select count(*) v_count
from all_users where username in ('SYS', 'SEER','XUSER','GRXU1','XXX01')
group by username);
COUNT
1
1
-----
select DECODE (v_count,NULL,'0',v_count) COUNT
from (select count(*) v_count
from all_users where username in ('SYS', 'SEER','XUSER','GRXU1','XXX01')
group by username);
COUNT
1
1
Or, probably I can create a procedure or function, the returns something like this...
set serveroutput on;
DECLARE
ctr number := 1;
v_count number;
TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
v_List Strings := Strings('SYS', 'SEER','XUSER','GRXU1','XXX01');
BEGIN
FOR ctr in v_List.FIRST .. v_List.LAST loop
select count(*) into v_count from all_users where username = v_List(ctr);
dbms_output.put_line ('Uname:'||v_List(ctr)||' Count:'||v_count);
end loop;
END;
Uname:SYS Count:1
Uname:SEER Count:1
Uname:XUSER Count:0
Uname:GRXU1 Count:0
Uname:XXX01 Count:0
PL/SQL procedure successfully completed.
I know there is still a lot of better ways to do this, so sue me..
Thanks,
Wilbert
[Updated on: Thu, 04 December 2008 03:52] by Moderator Report message to a moderator
|
|
|
Re: Count as array [message #362781 is a reply to message #362704] |
Thu, 04 December 2008 02:17   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
A simple right join with table containing list items as rows shall return the desired result.
If the table would be fixed one (preferably GLOBAL TEMPORARY one), it shall be filled before query.
This table may be also "created" in the query - then appropriate SQL collection type has to be available, as shown in the demonstration below.
SQL> create type username_tt as table of varchar2(30);
2 /
Type created.
SQL> with user_names as
2 ( select column_value username
3 from table( username_tt( 'SYS', 'SYSTEM', 'HR', 'OE', 'ORA12' ) ) )
4 select username, count(user_id)
5 from all_users right join user_names using (username)
6 group by username;
USERNAME COUNT(USER_ID)
------------------------------ --------------
ORA12 0
HR 0
SYSTEM 1
OE 0
SYS 1
Quote: | Assuming that USERNAME contains duplicates
|
I just wonder how you succeeded in created more users with the same name. May you show that trick?
|
|
|
|
|
|
Re: Count as array [message #362821 is a reply to message #362704] |
Thu, 04 December 2008 03:44   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | Instead of storing the value can we pass those a parameter
If a user found twice then count should be 2.
|
Sorry, I do not understand it at all. Maybe you shall post a test case similar to the one in my post. If it contains user-created table(s), post its CREATE command within a few INSERT statements to fill it with sample data (you may change names if you do not want to reveal it; similarly you may post only relevant columns to keep it short).
Found where? In the list you pass? In the table? What was the problem if it was the second case?
|
|
|
|
Re: Count as array [message #362838 is a reply to message #362827] |
Thu, 04 December 2008 04:43   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What table should we search in to get this?Please show us the data that you would like a query to run against to produce this data.
Here's a combination of a way to do what I think you want, and what we'd like you to give us:
drop table test_032;
create table test_032 (col_1 varchar2(30));
insert into test_032 values ('Name1');
insert into test_032 values ('Name3');
insert into test_032 values ('Name3');
insert into test_032 values ('Name3');
variable name_list varchar2(100);
execute :name_list:='Name1,Name2,Name3';
select s.col_1
,count(t.col_1)
from (select regexp_substr(:name_list,'[^,]+|',1,level) col_1
from dual
connect by level <= length(:name_list)-length(replace(:name_list,',',''))+1) s
,test_032 t
where t.col_1(+) = s.col_1
group by s.col_1
order by s.col_1
COL_1 COUNT(T.COL_1)
--------------------------------------- --------------
Name1 1
Name2 0
Name3 3
SQL>
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 19:05:25 CST 2025
|