Count the number of rows produced by a query
Hi, I have a query statement followed by the results it returns:
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments