Get a count of rows that meet condition

Question

SQLITE3

Task: get a data set that contains the following data - SEE NOTES BESIDE COLUMNS

SELECT DISTINCT DateTime(Rounded, 'unixepoch') AS RoundedDate, -- Rounded DateTime to the floor hour
            Count() AS Count, -- Count of items that registered within the above time
            CAST (avg(Speed) AS INT) AS AverageSpeed, -- Average table.Speed column data within the defined datetime
            Count() AS SpeederCount -- ?? WTF? [pseudo constraints: if Speed > Speedlimit then +1]
FROM RawSpeedLane AS sl
   INNER JOIN
   SpeedLaneSearchData AS slsd ON slsd.ParentId = sl.Id
   INNER JOIN
   Projects AS p ON p.ProjectId = sl.ProjectId
WHERE sl.ProjectId = 72
GROUP BY RoundedDate;

The SQL above is currently gives me all the data I need, EXECPT for the last column.

This last column is supposed to be the count of records where that pass specific criteria. The only way I have found to successfully do this is to build a sub query... Cool? okay, but the problem is the sub query takes 4 minutes to run because well... I suck at SQL :P No matter how many different ways I've tried to write it, it still takes forever.

Here is the long, but working version.

  SELECT DISTINCT RoundedDate,
                Count() AS Count,
                CAST (avg(Speed) AS INT) AS AverageSpeed,
                (
                    SELECT count() 
                      FROM RawSpeedLane AS slr
                     WHERE slr.ProjectId = 72 AND 
                           datetime( ( (strftime('%s', Start) - (strftime('%M', Start) * 60 + strftime('%S', Start) ) ) ), 'unixepoch') = sl.RoundedDate AND 
                           Speed > p.SpeedLimit
                )
                AS SpeederCount
  FROM SpeedLaneReportDataView AS sl
       INNER JOIN
       Projects AS p ON p.ProjectId = sl.ProjectId
 WHERE sl.ProjectId = 72
 GROUP BY RoundedDate;

I currently just tried this for the last column

(select Count() where sl.Speed > p.SpeedLimit)

but as expected, i got 1s and 0s im not really sure on what to do here. Any hints or help that lead me in the right direction is very much appreciated.

M. McLean · Answer 1 · 2016-07-11T02:11:06.730

2

I don't think SQLite has an IIF but CASE works.

This is a response to Backs answer, but I can't comment yet.

    SELECT DISTINCT DateTime(Rounded, 'unixepoch') AS RoundedDate, -- Rounded DateTime to the floor hour
                    Count() AS Count, -- Count of items that registered within the above time
                   CAST (avg(Speed) AS INT) AS AverageSpeed, -- Average table.Speed column data within the defined datetime
                   SUM(CASE WHEN Speed > SpeedLimit THEN 1 ELSE 0 END) AS SpeederCount
    FROM RawSpeedLane AS sl

edited Jul 11 '16 at 02:11

answered Jul 11 '16 at 02:04

M. McLean

84
3

Yes, you're right. SQLite doesn't have `IIF`. `CASE` is the right syntax. Thank you for taking a look and your answer. – Trae Moore Jul 11 '16 at 02:18
When summing boolean values, SUM(field) should work: https://stackoverflow.com/a/2510670/3991578 – Carsten Hagemann Feb 21 '19 at 19:10
SQLite has had an `IIF` function (see [https://www.sqlite.org/lang_corefunc.html#iif](https://www.sqlite.org/lang_corefunc.html#iif)) since version [3.32](https://www.sqlite.org/releaselog/3_32_0.html) – jgran Jun 17 '21 at 13:20

score 0 · Accepted Answer · answered Jul 11 '16 at 01:48

0

With SUM and IIF:

SELECT DISTINCT DateTime(Rounded, 'unixepoch') AS RoundedDate, -- Rounded DateTime to the floor hour
            Count() AS Count, -- Count of items that registered within the above time
            CAST (avg(Speed) AS INT) AS AverageSpeed, -- Average table.Speed column data within the defined datetime
            SUM(IIF(Speed > SpeedLimit, 1, 0)) AS SpeederCount
FROM RawSpeedLane AS sl

answered Jul 11 '16 at 01:48

Backs

24,430
5
58
85

Worked like a charm. Thank you for your answer. – Trae Moore Jul 11 '16 at 02:17

Get a count of rows that meet condition

2 Answers2