How to write sub queries in mysql

Question

I am joining three tables and taking count MY SQL FIDDLE. In this query I want to take one more count like total_trip that means I already join the trip_details table, in this table take all count that is total trip count, I am not able to write sub query.

SELECT COUNT(T.tripId) as Escort_Count,
(
    SELECT COUNT(*) FROM 
    (
        SELECT a.allocationId
        FROM escort_allocation 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 
FROM 
( 
    SELECT a.tripId FROM 
    trip_details a 
    INNER JOIN 
    escort_allocation b 
    ON a.allocationId = b.allocationId 
    GROUP BY a.allocationId 
) AS T

What exactly do you want your query to do? It's not clear from your statement, *i want to take one more count like `total_trip` that means i already join `trip_details` tables, in this table take all count that is total trip count*. — lurker, Mar 22 '18 at 12:46
@ lurker. check my [link](http://sqlfiddle.com/#!9/25879b/1) . from here i am getting two results like `Escort_Count` and `Adhoc_Trip_Count`,Now i want to take one more result is `total_trip`, total_trip means we have to take count * from `trip_details` table — Kani R, Mar 22 '18 at 12:54
@ Stanislovas Kalašnikovas, Escort_Count:`5` and Adhoc_Trip_Count:`2` and total_trip : `7` — Kani R, Mar 22 '18 at 12:55
update your question (not comment) add a proper data sample and the expected result — ScaisEdge, Mar 22 '18 at 12:57

Stanislovas Kalašnikovas · Accepted Answer · 2018-03-22T13:47:30.097

0

Use subquery in following:

Query:

  SELECT COUNT(T.tripId) as Escort_Count,
  (
      SELECT COUNT(*) FROM 
      (
          SELECT a.allocationId
          FROM escort_allocation 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

Results:

Escort_Count   Adhoc_Trip_Count   Total_Trip
5              2                  7

Check it out:

SQL Fiddle

edited Mar 22 '18 at 13:47

answered Mar 22 '18 at 13:19

Stanislovas Kalašnikovas

9,203
4
32
59

I tried your query it is not working properly like i changed `cab_allocation` table previously two `Adhoc Trip` happened,Now i changed `3`, now result should come `Escort_Count: 5` and `Adhoc_Trip_Count:3` and `Total_Trip: 7` but i am not getting my expected result [see your SQL fiddle](http://sqlfiddle.com/#!9/57986a/1) – Kani R Mar 22 '18 at 13:29
@KaniR so 5 + 3 = 8, not 7... Why `Total_Trip` should be `7`? – Stanislovas Kalašnikovas Mar 22 '18 at 13:31
Total_Trip means we have to take the count(*) FROM trip_details , no need sum of `Escort_Count + Adhoc_Trip_Count` – Kani R Mar 22 '18 at 13:43
@ Stanislovas Kalašnikovas, Your fiddle working fine, i want to add one more condition like `trip_details` table i added one more column called `tripDate`, i have to take above results only on today records,**main thing we have to use between condition in ** `tripDate` column and `trip_details` table kindly update your fiddle please – Kani R Mar 22 '18 at 13:59
@KaniR ask another question for this. – Stanislovas Kalašnikovas Mar 22 '18 at 14:11
@ Stanislovas Kalašnikovas, Possible means update this question only because again i have to wait for 90 Minutes to post new question – Kani R Mar 22 '18 at 14:21
@ Stanislovas Kalašnikovas,i posted new question what i asked like between condition kindly update your answer [New question Here](https://stackoverflow.com/questions/49434096/ho-to-use-between-condition-using-using-main-query-and-sub-quries?noredirect=1#comment85870244_49434096 ) – Kani R Mar 22 '18 at 16:58
@ Stanislovas Kalašnikovas,are you there please update your answer, i am waiting for your replay – Kani R Mar 22 '18 at 17:12

Sagar Gangwal · Answer 2 · 2018-03-22T13:56:58.073

        SELECT COUNT(DISTINCT Z.allocationId) Escort_Count,SUM(CASE WHEN allocationType='Adhoc Trip' THEN 1 END) Adhoc_Trip_Count  FROM (
           SELECT DISTINCT a.allocationId, allocationType FROM escort_allocation a 
           INNER JOIN cab_allocation c ON a.allocationId = c.allocationId 
           INNER JOIN trip_details TD ON a.allocationId = TD.allocationId 
           GROUP BY a.allocationId
        )Z;

OR

SELECT Escort_Count,Adhoc_Trip_Count,Adhoc_Trip_Count+Escort_Count TOTAL FROM(
            SELECT COUNT(DISTINCT Z.allocationId) Escort_Count,SUM(CASE WHEN allocationType='Adhoc Trip' THEN 1 END) Adhoc_Trip_Count,COUNT(D)  FROM (
               SELECT DISTINCT a.allocationId, allocationType,SUM(1) D FROM escort_allocation a 
               INNER JOIN cab_allocation c ON a.allocationId = c.allocationId 
               INNER JOIN trip_details TD ON a.allocationId = TD.allocationId 
               GROUP BY a.allocationId
            )Z
        )ZZ;

You can try above query .

Definitely it will help you.

Ya, it is working fine, but where is `totatrip_count`, we have to take from trip_details table — Kani R, Mar 22 '18 at 13:35
TOTAL means we have to take the count(*) FROM trip_details , no need sum of Escort_Count + Adhoc_Trip_Count — Kani R, Mar 22 '18 at 14:20

Gordon Linoff · Answer 3 · 2018-03-22T13:43:09.417

-1

Is this what you want?

SELECT td.tripId, COUNT(*) as total, SUM(ea.allocationType = 'Adhoc Trip') as adhocs
FROM trip_details td JOIN
     escort_allocation ea
     ON td.allocationId = ea.allocationId 
GROUP BY td.tripId;

edited Mar 22 '18 at 13:43

answered Mar 22 '18 at 13:04

Gordon Linoff

1,242,037
58
646
786

I tried your query it shows the following Error: `Unknown column 'c.allocationType' in 'field list'` – Nishant Gupta Mar 22 '18 at 13:08
1

LOL - I wonder how long it would take to write an application by posting questions on SO and pasting the answers into a text editor. – symcbean Mar 22 '18 at 13:12
@ Nishant Gupta [Check My Fiddle](http://sqlfiddle.com/#!9/25879b/1).from here i am getting two results like Escort_Count and Adhoc_Trip_Count,Now i want to take one more result is total_trip, total_trip means we have to take count * from trip_details table – Kani R Mar 22 '18 at 13:13

How to write sub queries in mysql

3 Answers3