CODEWITHSUNDEEP
sqloracle-database
user3109071
user3109071

Reputation: 27

Return number of records in SQL select union

I am trying to create a select query so that it meets a certain format. I need the string "Record Count" in first row. Then I also need the number of rows in second column row 1. Then I need to union it with another query

Record Count    125 
2134123

Here's what it looks like for sample in a csv that I want the output to appear as

Record Count,125
99902064
12312312

I tried the following code 

SELECT 'Record Count', count(select loginid
from employees)
FROM dual
union
select loginid
from employees

When I do this is puts the word record count in all the rows. I only want Record Count in row 1 and then next column have actual #. I was also considering just changing the column header to be "Record Count" but I couldn't figure out how to make the next column header a # i.e. use count(*).

Upvotes: 0

Views: 825

Answers (2)

psaraj12
psaraj12

Reputation: 5072

you can achieve the same removing the union using sqlplus spool.

Example of how to spool How do I spool to a CSV formatted file using SQLPLUS?

SELECT 'Record Count'|| ','|| count(select loginid
 from employees)
 FROM dual;
 select loginid
 from employees;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you need rows in a particular order, then you need to use order by. Here is one method:

select loginid, cnt
FROM (SELECT 'Record Count' as loginid, (select count(loginid) from employees) as cnt, 1 as ordering
      FROM dual
      union all
      select loginid, NULL, 2
      from employees
     ) t
order by ordering;

The subqueries in a union should also have the same columns, and the columns should be given names. And, I'm not aware that you can use a subquery as the argument to count().

For this form, this is a better way to write the query:

select loginid, cnt
FROM (SELECT 'Record Count' as loginid, count(loginid) as cnt, 1 as ordering
      FROM employees
      union all
      select loginid, NULL, 2
      from employees
     ) t
order by ordering;

Upvotes: 3

Related Questions