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 Go to next message
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 Go to previous messageGo to next message
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.. Razz

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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #362798 is a reply to message #362781] Thu, 04 December 2008 02:57 Go to previous messageGo to next message
raja.or.king
Messages: 24
Registered: November 2008
Location: GJ 5
Junior Member
i have also one doubt related to this as described

as shown in the code, when i select 4 users, it does not show anything about user 'sohan'
but if i select only one user 'sohan', then it shows result as 0.


SQL> select username, count(*) from user_details
  2  where username in ('naresh','sohil','tejas','sohan')
  3  group by username;

USERNAME
----------------------------------------------------------------
  COUNT(*)
----------
tejas
        32

sohil
       210

naresh
        37


SQL> select count(*) from user_details
  2  where username = 'sohan';

  COUNT(*)
----------
         0


why it is so?
Re: Count as array [message #362799 is a reply to message #362798] Thu, 04 December 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because the query is not the same:
SQL> select count(*) from all_users where username='toto' group by username;

no rows selected


Regards
Michel
Re: Count as array [message #362814 is a reply to message #362781] Thu, 04 December 2008 03:27 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Quote:

I just wonder how you succeeded in created more users with the same name. May you show that trick?



No it cant be. Thats why I wrote the word "assume"..

Instead of storing the value can we pass those a parameter
If a user found twice then count should be 2.
Re: Count as array [message #362821 is a reply to message #362704] Thu, 04 December 2008 03:44 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #362827 is a reply to message #362821] Thu, 04 December 2008 03:56 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Found where? In the list you pass? In the table? What was the problem if it was the second case?


In reply,

Pass list of values from client application as an in parameter list.


e.g, When we search with multiple keywords : SYS, SYSTEM, HR, OE, ORA12 the values we are passing should compare with the values of username column and return the result individually. If e.g, if ORA12 found 4 times count should be 4

for the multi search string the result should be

Count
__________
1
1
0
0
4
Re: Count as array [message #362838 is a reply to message #362827] Thu, 04 December 2008 04:43 Go to previous messageGo to next message
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> 
Re: Count as array [message #362891 is a reply to message #362838] Thu, 04 December 2008 07:20 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Yes this is what I was searching for. But my oracle version is 9.2

Re: Count as array [message #363135 is a reply to message #362704] Fri, 05 December 2008 03:18 Go to previous messageGo to next message
deb.b
Messages: 44
Registered: December 2008
Member
Quote:


regexp_substr(:name_list,'[^,]+|',1,level)


In 9i can break the string like above using substr?


I want to pass the search string as a parameter in proc

[Updated on: Fri, 05 December 2008 03:22]

Report message to a moderator

Re: Count as array [message #363142 is a reply to message #363135] Fri, 05 December 2008 03:49 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use a row generator and instr/substr to do it.

Regards
Michel

[Updated on: Fri, 05 December 2008 03:50]

Report message to a moderator

Previous Topic: passing control to sql script
Next Topic: External Tables
Goto Forum:
  


Current Time: Mon Dec 05 12:48:09 CST 2016

Total time taken to generate the page: 0.05659 seconds