How to use BETWEEN condition using using main query and sub queries

Question

I am using JOIN in 3 tables and getting the count and also mysql query working perfectly, now my question is I want to add one BETWEEN condition in my main query, here SQL FIDDLE you can check my table schema. In the trip_details table I have one column called tripDate using this column only I have to use BETWEEN condition in main query

Mysql query:

    SELECT COUNT(T.tripId) as Escort_Count,
  (
      SELECT COUNT(*) FROM 
      (
          SELECT a.allocationId
          FROM trip_details a 
          INNER JOIN cab_allocation c ON a.allocationId = c.allocationId 
          WHERE c.`allocationType` =  'Adhoc Trip'
          GROUP BY a.allocationId
      ) AS Ad

  ) AS Adhoc_Trip_Count,
  (SELECT COUNT(id) FROM trip_details) as Total_Count
  FROM 
  ( 
      SELECT a.tripId FROM 
      trip_details a 
      INNER JOIN 
      escort_allocation b 
      ON a.allocationId = b.allocationId 
      GROUP BY a.allocationId 
  ) AS T

Where is `BETWEEN` in your code example? Or, where are you wanting it? You should also explain what you are trying to achieve (perhaps by showing us the result you are getting vs the result you want) — devlin carnate, Mar 22 '18 at 16:44
Welcome to the site. While your question's formatting and the overall preparation is quite impressive for the first post, it is not clear from your current query what else you would like it to do. In fact, neither `BETWEEN` nor the column that it should constraint are mentioned in your query at all. — Sergey Kalinichenko, Mar 22 '18 at 16:48
BTW, you don't need `SELECT COUNT(*)` around the `SELECT a.allocationId` subquery, just use `SELECT COUNT(DISTINCT a.allocationId)` — Barmar, Mar 22 '18 at 16:48
@ devlin carnate, you saw my sql fiddle, from there `trip_details` table one column will be there called `tripDate`, i want to add condition between in my query, because now my query working perfect but there is no between condition, that means i have to select record only between two dates — Roobathi N, Mar 22 '18 at 16:55
Possible duplicate of [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) — RubioRic, Mar 22 '18 at 17:22
@ RubioRic, From this query how to implement `BETWEEN` condition — Roobathi N, Mar 22 '18 at 17:28
Add `WHERE tripdate BETWEEN '2018-01-01' AND '2018-02-15'` to each subquery. — Barmar, Mar 22 '18 at 20:25
@RoobathiN : no, I didn't see your Fiddle. I expect the content and context of your code to be here, as part of your question. — devlin carnate, Mar 22 '18 at 22:05
@ Barmar, Can you please update your query in my [SQL FIDDLE](http://sqlfiddle.com/#!9/99a87c) — Roobathi N, Mar 23 '18 at 03:08

score 0 · Answer 1 · answered Mar 23 '18 at 08:03

You have to add WHERE clause to each sub-query in following:

  SELECT COUNT(T.tripId) as Escort_Count,
  (
      SELECT COUNT(*) FROM 
      (
          SELECT a.allocationId
          FROM escort_allocation a 
          INNER JOIN trip_details b ON a.allocationId = b.allocationId 
          INNER JOIN cab_allocation c ON a.allocationId = c.allocationId 
          WHERE c.allocationType = 'Adhoc Trip' AND tripDate BETWEEN '20180315' AND '20180320'
          GROUP BY a.allocationId
      ) AS Ad

  ) AS Adhoc_Trip_Count,
  (SELECT COUNT(id) FROM trip_details WHERE tripDate BETWEEN '20180315' AND '20180320') as Total_Count
  FROM 
  ( 
      SELECT a.tripId FROM 
      trip_details a 
      INNER JOIN 
      escort_allocation b 
      ON a.allocationId = b.allocationId 
      WHERE tripDate BETWEEN '20180315' AND '20180320'
      GROUP BY a.allocationId 
  ) AS T

How to use BETWEEN condition using using main query and sub queries

1 Answers1

Linked