Count Records with SQL

I needed to count the number of records in a table I use for tracking tasks within an SQLite database. The table is named "Tasks" and, in addition to other columns has a Received column, where I record when I received a task and an Approved column, where I record the date I approved the task. If I only want to see the task numbers and the dates on which I received and approved tasks and I want the records ordered by approval date, I can use the following Structured Query Language (SQL) command:
Generic Category (English)120x600 
sqlite> select Task, Received, Approved from Tasks ORDER BY Approved;
TAS000000204813|2016-04-20|
TAS000000160855|2016-06-09|
TAS000000302389|2016-06-08|
TAS000000289579|2016-05-04|2016-05-06
TAS000000297385|2016-05-11|2016-05-11
TAS000000294896|2016-05-04|2016-05-12
TAS000000294136|2016-05-04|2016-05-12
TAS000000291991|2016-05-04|2016-05-13
TAS000000289882|2016-05-10|2016-05-13
TAS000000293978|2016-05-04|2016-05-16
TAS000000262109|2016-05-18|2016-05-19
TAS000000293289|2016-05-24|2016-05-24
TAS000000300934|2016-05-18|2016-05-24
TAS000000192984|2016-05-20|2016-05-24
TAS000000268274|2016-05-19|2016-05-24
TAS000000280549|2016-05-24|2016-05-24
TAS000000289548|2016-05-18|2016-05-24
TAS000000291959|2016-05-24|2016-05-24
TAS000000293970|2016-05-26|2016-05-26
TAS000000280569|2016-05-31|2016-05-31
TAS000000297347|2016-05-31|2016-05-31
TAS000000283262|2016-06-01|2016-06-07
TAS000000304021|2016-06-01|2016-06-07
TAS000000311853|2016-06-01|2016-06-07
TAS000000311293|2016-06-01|2016-06-07
TAS000000312411|2016-06-08|2016-06-08
TAS000000309299|2016-06-09|2016-06-09
TAS000000270128|2016-06-09|2016-06-09
TAS000000255397|2016-06-13|2016-06-13
TAS000000295302|2016-06-09|2016-06-14
TAS000000299922|2016-06-08|2016-06-14
TAS000000298570|2016-06-14|2016-06-14
TAS000000315505|2016-06-08|2016-06-14
sqlite>

I can count all of the records in the table with the SQL command below:

sqlite> select COUNT(*) FROM Tasks;
33
sqlite>

Within the table, I have a field named "Approved" that I use to track the approval date for tasks; I store the date as a text field, since SQLite doesn't have a storage class specifically for dates. If I want to know how many tasks I've approved since a specific date, I can use a command like the one below to determine the number approved since the specified date.

sqlite> select COUNT(*) FROM Tasks WHERE Approved > "2016-06-07";
8
sqlite>

If I want to determine the number of tasks that haven't been approved, i.e. those that have a null value in the record for that field, I can use a command like the one below:

sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved IS  Null;
1
sqlite>

Even though it appears there are 3 tasks without an approval date in the table based on the first command I issued to display the data, only one has a null value, the fields for the other two are empty, but not null, as I can see using DB Browser for SQLite.

DB Browser for SQLite

If I tab through the fields while entering records in DB Browser for SQLite, the null value is replaced with an empty, but not null field. If I check to see if there is some unprintable value in the cell using the hex function to display the contents of the fields in hexadecimal format, I don't see anything displayed for the Approved field.

sqlite> SELECT hex(Task), hex(Approved) FROM Tasks WHERE Approved IS "";
544153303030303030313630383535|
544153303030303030333032333839|
sqlite>

I can see which records have a null value or have no date in that field with the commands below:

sqlite> SELECT Task FROM Tasks WHERE Approved IS NULL;
TAS000000204813
sqlite> SELECT Task FROM Tasks WHERE Approved IS "";
TAS000000160855
TAS000000302389
sqlite>

If I want to know on how many days I approved tasks, I can use the command below, which will only count distinct days, i.e., if I approved 5 tasks on a particular day, that day will only be counted once.

sqlite> select COUNT(DISTINCT Approved) FROM Tasks;
15
sqlite>

If I want to see a list of those days, I can use the command below.

sqlite> SELECT DISTINCT Approved FROM Tasks;

2016-05-06
2016-05-11
2016-05-12
2016-05-16
2016-05-13
2016-05-19
2016-05-24
2016-05-26
2016-05-31
2016-06-07
2016-06-08

2016-06-09
2016-06-14
2016-06-13
sqlite>

I can see that the records that have an empty or null value for the approval date are each listed once (there are two records with an empty field and one where the value is null).

If I want to display all of the tasks that are approved, i.e., where there is an approval date and order the output by approval date, I can use the command below:

sqlite> SELECT Task, Approved FROM Tasks WHERE Approved IS NOT "" AND Approved IS NOT NULL ORDER BY Approved;
TAS000000289579|2016-05-06
TAS000000297385|2016-05-11
TAS000000294896|2016-05-12
TAS000000294136|2016-05-12
TAS000000291991|2016-05-13
TAS000000289882|2016-05-13
TAS000000293978|2016-05-16
TAS000000262109|2016-05-19
TAS000000293289|2016-05-24
TAS000000300934|2016-05-24
TAS000000192984|2016-05-24
TAS000000268274|2016-05-24
TAS000000280549|2016-05-24
TAS000000289548|2016-05-24
TAS000000291959|2016-05-24
TAS000000293970|2016-05-26
TAS000000280569|2016-05-31
TAS000000297347|2016-05-31
TAS000000283262|2016-06-07
TAS000000304021|2016-06-07
TAS000000311853|2016-06-07
TAS000000311293|2016-06-07
TAS000000312411|2016-06-08
TAS000000309299|2016-06-09
TAS000000270128|2016-06-09
TAS000000255397|2016-06-13
TAS000000295302|2016-06-14
TAS000000299922|2016-06-14
TAS000000298570|2016-06-14
TAS000000315505|2016-06-14
sqlite>

And if I only want to count the number of approved tasks or count the number of distinct days on which I approved a task, I can use the commands below:

sqlite> SELECT COUNT(Approved) FROM Tasks WHERE Approved IS NOT "" AND Approved IS NOT NULL ORDER BY Approved;
30
sqlite> SELECT COUNT(DISTINCT Approved) FROM Tasks WHERE Approved IS NOT "" AND Approved IS NOT NULL ORDER BY Approved;
14
sqlite>

Suppose I only wanted to see those records where the approval occurred during the last week, i.e., the last 7 days; I could use the date function as in the command below:

sqlite> select Task, Approved FROM Tasks WHERE Approved > (SELECT date('now', '-7 day')) ORDER BY Approved;
TAS000000309299|2016-06-09
TAS000000270128|2016-06-09
TAS000000255397|2016-06-13
TAS000000295302|2016-06-14
TAS000000299922|2016-06-14
TAS000000298570|2016-06-14
TAS000000315505|2016-06-14
sqlite>

And if I wanted to count all of the records where I approved the task in the last 7 days, i.e., from Wednesday June 8, 2016 to Tuesday June 14, 2016, given that today is Tuesday June 14, I could use the command below:

sqlite> select COUNT(*) FROM Tasks WHERE Approved >= (SELECT date('now', '-7 day')) ORDER BY Approved;
8
sqlite>

I used greater than or equal to for the test, i.e., ">=", rather than greater than, i.e., ">", so that June 8 is included, also, to get the last 7 days worth of approvals, which includes today.

 

TechRabbit ad 300x250 newegg.com

Justdeals Daily Electronics Deals1x1 px