title: Fetching rows within set time constraints
body: <p>The table:</p>
  <pre><code>trips
  ----
  id, signin, first_name, last_name, ~~
  id, signin, first_name, ~~~
  etc, etc..
  </code></pre>
  <p>The query I've got so far:</p>
  <pre><code>SELECT DISTINCT
  (SELECT date(signin,'weekday 0','-7 day') ) as start_of_week,
  (SELECT date(signin,'weekday 0','-1 day') ) as end_of_week
  FROM trips
  </code></pre>
  <p>Returns the first and last day of each row's signin field as start_of_week and end_of_week.</p>
  <p>Runs just fine.</p>
  <p>But when I try to run more advanced queries to simply count the many rows in between the the start_of_week and end_of_week it returns the total count instead, inaccurate dates, ignores the other week's patterns found. I tried fooling with order by and group by but I want to understand where I'm going wrong rather than just playing with values.</p>
  <pre><code>SELECT DISTINCT
  (SELECT date(signin,'weekday 0','-7 day') ) as start_of_week,
  (SELECT date(signin,'weekday 0','-1 day') ) as end_of_week,
  COUNT(*) as total_rows
  FROM trips
  GROUP BY date(signin) BETWEEN start_of_week AND end_of_week
  </code></pre>
  <p>start_of_the_week being Sunday
  end_of_the_week being Saturday</p>
  <p>Is the groundfloor I keep finding myself at. This one only returns one of the weeks start-end with the total count of them all, and it returns it twice rather than distinctively. I'm working in SQLite.</p>
  <p>Thankyou</p>