MySQL Quick Hit: Counting Records

Within the context of our technical work, we sometimes just need a simple number to guide us along to help answer our questions.

For example, we may be curious about the number of records that were created per day. Or, perhaps we want to know how many were modified, etc. Filters and reports within Sugar can help us get some answers, but we can also do some nifty things by way of SQL queries. 

Let us take a look at the following query:

select distinct(date(date_entered)), count(*) from opportunities

where deleted = '0'

group by date(date_entered);

The above will generate output similar to the following:

Notice that the results provide us an easy to read snapshot of the data we are seeking. Obtaining similar numbers for other criteria is a simple matter of changing the field being analyzed by the DISTINCT() function and GROUP BY clause. Below is an example of a modified version that would give us the number of records that were modified per day, rather than entered as in the original example.

select distinct(date(date_modified)), count(*) from opportunities

where deleted = '0'

group by date(date_modified);

May this help simplify your work.