Re: Counting Records

"Tom Miskiewicz" <miskiewicz2_at_yahoo.com> wrote :

> What is the most efficient way of counting records in a table?

SELECT count(*) FROM table

Alternatively:

SELECT count(col1) FROM table

> I want to count records in a table that match my query. How do I do that?

SELECT count(*) FROM table
WHERE col1 = 'some value'

> I guess that select count(1) from table_name where fieldA='something' isn't
> the most elegant thing I can do?

Why? Simplicity does have its own elegance.

Also, instead of focusing on SQL, rather first look at *what* you're trying to achieve and *why*. Then match the db design and SQL to it.

Quite often a SELECT count(*) on its own is pretty much meaningless. For example, the customer has a 100 invoices. What does that mean? What business requirement does it satisfy to display the number 100 next to that customer's name? That is not business information - that's raw data. Which cannot contribute to the business decision (or process flow) that the end-user is responsible for.

And when you start talking aggregation and the like, materialised views could be a better solution.

Unless you want to show off the speed of your database with a parlour trick of doing a SELECT count(*) on a VLT of 77+ million rows in less than 7 seconds. ;-)

--
Billy