CODEWITHSUNDEEP
oracle-database
Suniel
Suniel

Reputation: 1499

Displaying data with total no of data

I have a table named customer in oracle database. Now I have to select the data from customer table like this.

   ID    Name   Address      DOB

   1     Ram      goa      2/2/1988
   2     Rahul    ktm      3/3/1987
   3     Hari     pkr      4/4/1986

 TOTAL                       3

There is no problem in getting the data using select query from table. But I got problem in displaying the Total count. I have to display only using query. Please help soon.

Thanks In advance ...

Upvotes: 0

Views: 66

Answers (3)

haki
haki

Reputation: 9759

try this

select  ID ,Name ,Address ,DOB, count(*)
from    mytable
group by rollup((ID ,Name ,Address ,DOB))

Upvotes: 0

Rahul
Rahul

Reputation: 5636

You may also use NUM_ROWS in oracle

SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = table_name;

or you may try for COUNT as well like

SELECT COUNT(*) AS TOTALROWS FROM tablename;

If you want to select all rows values along with count then try

SELECT ID,Name,Address,DOB,COUNT(*) as TOTALROWS
FROM tablename;

You can complete this task using Union All or Union like

select ID,Name,Address,DOB from tablename
union all
select 'Toal','','',count(*) from tablename;

or

select ID,Name,Address,DOB from tablename
union
select 'Toal','','',count(*) from tablename;

Hope it works.

Upvotes: 1

Ron.B.I
Ron.B.I

Reputation: 2766

In order to display total number of rows you should use:

SELECT COUNT(*) AS TOTAL FROM customers

If you are trying to have a single select query that will display the content and beneath it the total amount of content it is impossible IMHO / very unproper usage of SQL. your queries have different columns and that is sufficient to determine it cannot be done in a singe query.

If you want however to have the data in a separate column this can be done:

SELECT *
FROM customers,(SELECT COUNT(*) AS TOTAL FROM CUSTOMERS)

will add the total column to your query output.

And for or a single SELECT query:

SELECT id,name,address,dob,COUNT(*) as TOTAL
FROM customer

See fiddle for full code: Fiddle

Upvotes: 0

Related Questions