SQL Combining Multiple SELECT Statements

Question

I am trying to build an SQLite query that will collect statistics from a single table.

The table holds a log, of sorts, with several entries per day. I need to get a separate row for each day within the search parameters and then compile the totals of rows within those dates with certain boolean values.

Here is the query I have so far:

SELECT DATE(DateTime) AS SearchDate,
     (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS Total,
    (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE NoMarket = 1
                AND DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS NoMarkets,
    (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE Complaint = 1
                AND DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS Complaints,
    (SELECT COUNT() AS Total
                FROM CallRecords
                WHERE Voicemail = 1
                AND DATE(DateTime)
                BETWEEN '2017-08-27' AND '2017-09-02'
                GROUP BY DATE(DateTime)
                ORDER BY Total DESC) AS Voicemails
FROM CallRecords
WHERE DATE(DateTime) BETWEEN '2017-08-27' AND '2017-09-02'
GROUP BY SearchDate

And the output:

8/28/2017   175 27      11
8/29/2017   175 27      11
8/30/2017   175 27      11
8/31/2017   175 27      11
9/1/2017    175 27      11

As you can see, it is properly getting each individual date, but the totals for the columns is incorrect.

Obviously, I am missing something in my query, but I am not sure where. Is there a better way to perform this query?

EDIT: I have looked into several of the other questions with near-identical titles here, but I have not found anything similar to what I'm looking for. Most seem much more complicated than what I'm trying to accomplish.

Answer 1

It looks like you have a mess of columns in your CallRecords table with names like Complaint and Voicemail, each of which classifies a call.

It looks like those columns have the value 1 when relevant.

So this query should probably help you.

SELECT DATE(DateTime) AS SearchDate,
       COUNT(*) AS Total,
       SUM(NoMarket = 1) AS NoMarkets,
       SUM(Complaint = 1) AS Complaints,
       SUM(Voicemail = 1) AS Voicemails
  FROM CallRecords
 WHERE DateTime >=  '2017-08-27'
   AND DateTime <   '2017-09-02' + INTERVAL 1 DAY
 GROUP BY DATE(DateTime)

Why does this work? Because in MySQL a Boolean expression like Voicemail = 1 has the value 1 when it's true and 0 when it's false. You can sum those values up quite nicely.

Why is it faster than what you have? Because DATE(DateTime) BETWEEN this AND that can't exploit an index on DateTime.

Why is it correct for the end of your date range? Because DateTime < '2017-09-02' + INTERVAL 1 DAY pulls in all the records up until, but not including, midnight, on the day after your date range.

If you're using Sqlite, you need AND DateTime < date('2017-09-02', '+1 day'). The + INTERVAL 1 DAY stuff is slightly different there.