How to count records from an specific query in Orale SQL

November 17, 2022

This may be very basic, but I’m trying to get the exact number of records for a query in Oracle SQL.
Since the platform doesn’t allow to get big files as an export, I need to divide the output in parts, but I want to know how many records I have in each year for example.
This is the query:

select a.item1, c.item2, c.item3, d.date1, d.date2, c.amount1, c.amount2, c.ID1, c.ID2
from Table1 a, Table2 b, Table3 c, Table4 d
where a.ID1 = b.ID1
and b.ID1 = c.ID1
and c.ID1 = d.ID1
and (d.ID4 = 'abc1'
or d.ID4 = 'abc2'
or d.ID4 = 'abc3')
and trunc(d.date1) between to_date('20210101','YYYYMMDD') and to_date('20211231','YYYYMMDD')

The query runs fine in test mode, but in prod I get that my output is too big, that´s why I want to know how many records I get per year.

I’m expecting to see how many records per year I have with this specific query.

>Solution :

Replace select list with count(*):

select count(*)
from Table1 a, Table2 b, Table3 c, Table4 d
where a.ID1 = b.ID1
and b.ID1 = c.ID1
and c.ID1 = d.ID1
and (d.ID4 = 'abc1'
or d.ID4 = 'abc2'
or d.ID4 = 'abc3')
and trunc(d.date1) between to_date('20210101','YYYYMMDD') and to_date('20211231','YYYYMMDD')