How to use group by and count in mysql

Question

Here I am having 3 table using I have to take the count

trip_details

id    allocationId       tripId

1         7637             aIz2o
2         7626             osseC
3         7536             01LEC
4         7536             78w2w
5         7640             S60zF
6         7548             ruaoR
7         7548             Qse6s

escort_allocation

id       allocationId    escortId

3          7637            1
4          7626            1
5          7627            1
6          7536            1
7          7640            1
7          7548            1

cab_allocation

 allocationId    allocationType

 7637             Daily Trip
 7626             Daily Trip
 7627             Daily Trip
 7536              Adhoc Trip
 7640               Adhoc Trip
 7548               Daily Trip

Using above table I have to get the count, I tried but it is not happening my expected results.

I tried sql query

    SELECT a.`tripId` 
FROM  `trip_details` a
INNER JOIN escort_allocation b ON a.`allocationId` = b.`allocationId` 
GROUP BY a.`allocationId` 
LIMIT 0 , 30

I am getting like this

tripId

01LEC
ruaoR
osseC
aIz2o
S60zF

total 6 tripId i got ,so now I want to take the count so I am using this query

    SELECT COUNT(*)
FROM  `trip_details` a
INNER JOIN escort_allocation b ON a.`allocationId` = b.`allocationId` 
GROUP BY a.`allocationId` 
LIMIT 0 , 30

but this query is not working.I am getting results like below

MY MYSQL TABLES AND VALUES LOOK LIKE THIS

    CREATE TABLE `trip_details` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `allocationId` int(11) NOT NULL,
 `tripId` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `tripId` (`tripId`),
 KEY `allocationId` (`allocationId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;

INSERT INTO trip_details
    (id, allocationId, tripId)
VALUES
    (1, 7637, '00SwM'),
    (2, 7626, '00SwM'),
    (3, 7536, '00SwM'),
    (4, 7536, '01hEU'),
    (5, 7640, '01hEU'),
    (6, 7548, 'IRZMS'),
    (7, 7548, 'IRZMS');




CREATE TABLE `escort_allocation` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `allocationId` int(11) NOT NULL,
 `escortId` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ;

INSERT INTO escort_allocation
    (id, allocationId, escortId)
VALUES
    (1, 7637, 'ssda'),
    (2, 7626, 'adad'),
    (3, 7627, 'sfsaf'),
    (4, 7536, 'ssaf'),
    (5, 7640, 'asf'),
    (6, 7548, 'a3r');



CREATE TABLE `cab_allocation` (
 `allocationId` int(11) NOT NULL AUTO_INCREMENT,
 `allocationType` enum('Daily Trip','Adhoc Trip') NOT NULL,
 PRIMARY KEY (`allocationId`)
) ENGINE=InnoDB AUTO_INCREMENT=7695 DEFAULT CHARSET=latin1;

INSERT INTO cab_allocation
    (allocationId, allocationType)
VALUES
    (7637, 'Daily Trip'),
    (7626, 'Daily Trip'),
    (7627, 'Daily Trip'),
    (7536, 'Adhoc Trip'),
    (7640, 'Adhoc Trip'),
    (7548, 'Daily Trip');

@ Rakibul Islam escort_allocation table i am having total 6 allocationId but i am using INNER JOIN so result should come '5' — Kumaresan M
– Kumaresan M, Commented Mar 21, 2018 at 12:58

Jenish · Accepted Answer · 2018-03-21 13:24:01Z

1

You can try this

 SELECT COUNT(DISTINCT (a.`tripId`))
 FROM  `trip_details` a
 INNER JOIN escort_allocation b ON a.`allocationId`=b.`allocationId`
 LIMIT 0 , 30

because of GROUP BY there is separate count for all allocationId.

edited Mar 21, 2018 at 13:24

answered Mar 21, 2018 at 13:05

Jenish

5334 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Doron Yakovlev Golani · Accepted Answer · 2018-03-21 15:41:14Z

1

With this, you should get the tripid and the amount:

SELECT COUNT(a.tripId) as total, a.tripId as tripId 
FROM trip_details a INNER JOIN escort_allocation b 
ON a.allocationId = b.allocationId 
GROUP BY a.allocationId LIMIT 0 , 30

edited Mar 21, 2018 at 15:41

Doron Yakovlev Golani

5,50810 gold badges43 silver badges64 bronze badges

answered Mar 21, 2018 at 12:51

Eva

1731 silver badge14 bronze badges

2 Comments

Kumaresan M Over a year ago

I am getting results like this COUNT(a.tripId) 2 2 1 1 1

Eva Over a year ago

SELECT COUNT(a.tripId) as total, a.tripId as tripId FROM trip_details a INNER JOIN escort_allocation b ON a.allocationId = b.allocationId GROUP BY a.allocationId LIMIT 0 , 30

UkFLSUI · Accepted Answer · 2018-03-23 05:51:02Z

0

You may use:

SELECT COUNT(DISTINCT a.allocationId)
FROM
trip_details a
INNER JOIN
escort_allocation b
ON a.allocationId = b.allocationId

Previously you used COUNT(*) and also used GROUP BY so the counts of rows you were getting are from individual groups.

Update-2:

SELECT 
(
    SELECT COUNT(*) FROM trip_details
) AS Total_Trip_Count,
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

edited Mar 23, 2018 at 5:51

answered Mar 21, 2018 at 13:08

UkFLSUI

5,6886 gold badges39 silver badges50 bronze badges

20 Comments

Kumaresan M Over a year ago

I tried this code SELECT COUNT(T.tripId) as Escort_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 it is working

Kumaresan M Over a year ago

But again i have to join one more table 'cab_allocation' using allocation id and i have to take the count. howmany allocationType we are using Adhoc Trip

UkFLSUI Over a year ago

In that case you may use the 2nd part of my answer.....just replace the query within the first bracket with your new select and inner join query

Kumaresan M Over a year ago

i tried your code but i am getting error Every derived table must have its own alias,Kindly update your code like both count, 1.Escort_Count 2.Adhoc_Count

UkFLSUI Over a year ago

You need Escort_Count and Adhoc_Count by same query or by two diferent queries

|

Collectives™ on Stack Overflow

How to use group by and count in mysql

3 Answers 3

Comments

2 Comments

20 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

20 Comments

Related