Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I get a count in a query being used?

Posted on 2013-10-25
12
Medium Priority
?
545 Views
Last Modified: 2013-10-25
Oracle 11g.  No PL/SQL used for this query.

How do I get a count of how many rows retrieved from the result set
and have it displayed as a column within the query?
Say that I run the query example below, if there was 140 columns and
a "big" where clause and it retrieved 20 rows.  I want to display the count
within the column - RowCount below.
Thanks.

Select column1,
           RowCount,
           column2,
           column3,
           .
           .
           .
          column140
From MyTable1,
          AnotherTable,
          AndAnother
Where Clause Statements..
..
..
0
Comment
Question by:Machinegunner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39601563
Try this:

Select column1,
           count(*) over() RowCount,
...
0
 

Author Comment

by:Machinegunner
ID: 39601619
That did it!  THANK YOU.
0
 

Author Comment

by:Machinegunner
ID: 39601634
Question, now would that be a total count if I had a couple of union statements?
Meaning I need a total count of all the records retrieved, not individual select statements.
Thanks.

Select
  Count(*) over() As RowCount
  .

UNION
Select
 .
 .
UNION
Select
 .
 .
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601717
Wrap all the unions in an inline view, then do the count outside the view:

select dummy, count(*) over() from
(
     select dummy from dual
     union
     select dummy from dual
     union
     select dummy from dual
);
0
 

Author Comment

by:Machinegunner
ID: 39601872
Sorry, getting this error ORA-00904: "DUMMY": invalid identifier, when running this:

Select dummy, Count(*) Over() As RowCount From
(
SELECT
    (select dummy from dual),
    Col1,
    .
    Col140
UNION
SELECT
    (select dummy from dual),
    Col1,
    .
    Col140
UNION
SELECT
    (select dummy from dual),
    Col1,
    .
    Col140
);
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601889
dummy is a column in the DUAL table.

That was just an example of how you do it.

You need to move your entire unioned select statement inside of parans, then select every column and the COUNT in the outside select.

For example, you have:
create table tab1(col1 char(1), col2 char(1), col3 char(1));
create table tab2(col4 char(1), col5 char(1), col6 char(1));

The it goes:

select mycol1, mycol2, mycol3, count(*) over() from
(
select col1 mycol1 col2, mycol2, col3 mycol3 from tab1
union
select col4, col5, col5 from tab2
);


In a union the first select names the columns for the inline view.  As you can see, just for grins, I aliased the columns with 'my'.
0
 

Author Comment

by:Machinegunner
ID: 39601905
Whew!  That'll be a lot of columns on the outside!  140 of them  :-)

select col1, col2, col3........col140, count(*) over() from
(
SELECT --Outpatients...
    Col1,
    .
    Col140
UNION
SELECT --Inpatients...
    Col1,
    .
    Col140
SELECT  --Alternate Care...
    Col1,
    .
    Col140
);
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601916
>>That'll be a lot of columns on the outside!  140 of them  :-)

Let me save you some time...  alias the inline view then you can select *.

I also corrected several typos I had in the above example (I edited that post as well).

Here's a complete test case.

drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 char(1), col2 char(1), col3 char(1));
create table tab2(col4 char(1), col5 char(1), col6 char(1));

insert into tab1 values('a','b','c');
insert into tab1 values('d','b','f');

insert into tab2 values('d','b','f');
commit;


select t.*, count(*) over() from
(
select col1 mycol1, col2 mycol2, col3 mycol3 from tab1
union
select col4, col5, col6 from tab2
) t;

Open in new window

0
 

Author Comment

by:Machinegunner
ID: 39601938
Sorry to keep bugging you on a fine Friday afternoon before the weekend!
It seems that it doesn't like the 't.*'...

ORA-00918: column ambiguously defined

select t.*, count(*) over() from
(
SELECT --Outpatients...
    Col1,
    .
    Col140
UNION
SELECT --Inpatients...
    Col1,
    .
    Col140
UNION    
SELECT  --Alternate Care...
    Col1,
    .
    Col140
) t;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601964
>>Sorry to keep bugging you

No problem!

>>on a fine Friday afternoon

It's almost 8PM where I am....  beer opened a while ago!   ;)

I've got all night...

>>ORA-00918: column ambiguously defined

I assume you tested the massive union query all by itself?

What is your Oracle version (all 4 numbers please, like 10.2.0.4).

I ran what I posted against 11.2.0.3.

Can you try my test case as-posted against a dev/test database on the same version you are running?
0
 

Author Comment

by:Machinegunner
ID: 39601965
I added all 140 columns on the top and works like a charm.  Thanks again!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601971
No problem.

I was thinking about adding all the columns...  Should not have even been that much typing (sorry if it was).

Copy/paste the first query in the union and you have all the columns with all the names.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question