How to do sum of column when there is 1million records

Question

How to get sum of column soon when there is 1million records or than that in the table?

Answer 1

Yes, doing a sum when there are a million records in the table takes the database longer than doing a sum with a few thousand records.  If you have performance problems, you might have to add an index.  For example, suppose you had this SQL:

select SUM(sale_price)
from sales
where store_id = 57

If you have millions of records, you might need to put an index on the store_id column to improve performance. 

Answer 2

As Andrew mentioned, you don't need to fetch any records. Just use SUM function. 

Answer 3

Hi,
I mean to ask it will take more time to fetch the record from millions of record?

Answer 4

If you just want the sum, nothing else, you should use a simple SQL query to let the database do it.  You should NOT try fetching 1 million records to the web server and then try to sum them there.  That would be slow. 

select SUM(sale_price) from sales

The database can do this kind of thing very efficiently.

Answer 5

That data should be in a partitioned table but that option is available only in the Enterprise, Development or Eval version of SQL Server.

Otherwise you can use a partitioned view which logically splits a large table into smaller tables.

Answer 6

So you saying there will be no filters applied for fetching data. Check my reply on your other thread, use Partitioned Table best practices. 1 million records is nothing now days :) ..