Q
Problem solve Get help with specific problems with your technologies, process and projects.

Count the number of rows produced by a query

Hi, I have a query statement followed by the results it returns:

select ID, type, count(*) as "count"
from personal_time_event
where logdate = '2005-04-15'
group by ID, type
order by ID asc, type desc

ID    type   count
 1  1402 P10    6
 2  1403 P10   43
 3  1404 P20  231
 ----  snip  ----
51  2521 P20   69
52  2522 P10   63

As you can see, 52 rows are returned. What is the single query statement to retrieve this count of 52 rows?? Thanks in advance.

You're going to smile when you see this, because it's so simple:

select count(*) as rows
  from ( 
       select ID, type
         from personal_time_event
        where logdate = '2005-04-15'
       group by ID, type
       ) as dt 

Notice how the subquery no longer includes either the count for each individual ID and type combination, or the ORDER BY clause. They aren't necessary in the subquery, because all you really want is a count of the rows produced by the subquery.

There's also another way to get the same answer:

select count(distinct ID, type) as rows
  from personal_time_event
 where logdate = '2005-04-15'

You don't often see two columns inside a COUNT(DISTINCT...) expression, but it gives exactly what you expect -- at least, it does in those databases that support it. In those that don't, you'll have to concatenate the values, and it's perfectly okay to do so, because all you want is to count the number of unique combinations:

select count(distinct ID || type) as rows
  from personal_time_event
 where logdate = '2005-04-15'

Depending on your database, you might need a different concatenation operator, and you may or may not need to worry about implicit data conversion if ID is numeric and type isn't. Test it and see.

This was last published in April 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close