Home » SQL & PL/SQL » SQL & PL/SQL » Analytic with Count
Analytic with Count [message #215712] Tue, 23 January 2007 11:07 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I'm not sure if this can be done or not but I thought I would ask.

Question:

Can you write an SQL statement that will return multiple counts with an analytic function?

If I wanted to count the X, Y and Z items within a single SQL statement, can I do that? Or would I have to have 3 different SQL statements for each count?

CREATE TABLE CONFERENCE_ENROLLMENT
(
  CONFERENCE_ID        NUMBER,
  CONFERENCE_SEQUENCE  NUMBER,
  TOPIC_ID             NUMBER
)

Data:
1,1,1
1,1,1
1,1,2
1,1,3
1,1,3

Anyway to do:

select count(topic_id),
       count(topic_id),
       count(topic_id)
  into topic_id1_count,
       topic_id2_count,
       topic_id3_count
  from conference_enrollment
    where conference_id       = 1 and
          conference_sequence = 1

topic_id1_count = 2
topic_id2_count = 1
topic_id3_count = 2




Instead of doing:

select count(topic_id)
  into topic_id1_count
  from conference_enrollment
    where conference_id       = 1 and
          conference_sequence = 1 and
          topic_id            = 1

select count(topic_id)
  into topic_id2_count
  from conference_enrollment
    where conference_id       = 1 and
          conference_sequence = 1 and
          topic_id            = 2

select count(topic_id)
  into topic_id3_count
  from conference_enrollment
    where conference_id       = 1 and
          conference_sequence = 1 and
          topic_id            = 3

Re: Analytic with Count [message #215713 is a reply to message #215712] Tue, 23 January 2007 11:15 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is wrong with such a simple solution?
SELECT topic_id, COUNT(*)
FROM CONFERENCE_ENROLLMENT
GROUP BY topic_id
ORDER BY 1;
Optionally, add another table columns to the SELECT column list, along with GROUP BY column list.
Re: Analytic with Count [message #215721 is a reply to message #215713] Tue, 23 January 2007 11:35 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I guess that would work.

How would that work if I'm selecting INTO variables? You see, I need the count for each topic_id. If that topic_id is not in the table then the variable for that topic_id needs to be zero. I wasn't very clear on that.

I need the counts broken by their topic_id but each count needs to be in their own variable, such as, topic_id1_count, topic_id2_count and topic_id3_count. That's what i'm trying to figure out without having to do a single SQL statement for each count.
Re: Analytic with Count [message #215738 is a reply to message #215712] Tue, 23 January 2007 13:27 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
To get all of the values in one query, you could store them into an array. Or you could just iterate over a cursor loop. There are actually quite a few ways to do this depending on the size of the data you are working with and the oracle version. It would be no different than working with any multi-row result set.

To get zero values for topic_ids that don't exist in the table though, you would need to outer join to some sort of master topic id table.

MYDBA@orcl > create table master(id) as select level from dual connect by level <= 5;

Table created.

MYDBA@orcl > create table entries (id number, other_stuff char(10));

Table created.

MYDBA@orcl > insert into entries values (1,'x');

1 row created.

MYDBA@orcl > insert into entries values (1,'y');

1 row created.

MYDBA@orcl > insert into entries values (1,'z');

1 row created.

MYDBA@orcl > insert into entries values (3,'x');

1 row created.

MYDBA@orcl > insert into entries values (5,'x');

1 row created.

MYDBA@orcl > insert into entries values (5,'y');

1 row created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > select * from master;

        ID
----------
         1
         2
         3
         4
         5

MYDBA@orcl > select * from entries;

        ID OTHER_STUF
---------- ----------
         1 x
         1 y
         1 z
         3 x
         5 x
         5 y

6 rows selected.

MYDBA@orcl > select id, count(*) from entries group by id;

        ID   COUNT(*)
---------- ----------
         1          3
         5          2
         3          1

MYDBA@orcl > select master.id, count(entries.id) from master, entries
  2  where master.id = entries.id (+) group by master.id order by master.id;

        ID COUNT(ENTRIES.ID)
---------- -----------------
         1                 3
         2                 0
         3                 1
         4                 0
         5                 2

MYDBA@orcl >


Re: Analytic with Count [message #215740 is a reply to message #215712] Tue, 23 January 2007 13:35 Go to previous message
Duane
Messages: 452
Registered: December 2002
Senior Member
That's an idea. I'm working with 10g so I'll see what I can come up with using an array.

Thanks.
Previous Topic: sql query to find the string
Next Topic: Column Keyword
Goto Forum:
  


Current Time: Sun Dec 04 06:23:15 CST 2016

Total time taken to generate the page: 0.16696 seconds