CODEWITHSUNDEEP
mysqlsqljoin
user12587364
user12587364

Reputation:

MySQL COUNT CASE (count the total occurrance of value in a column)

I would like to count the number of GPS points in plt_distinct table based on the mode of transportation in labels table:

 CREATE TABLE `plt_distinct` (
  `directory` varchar(10) NOT NULL DEFAULT '',
  `latitude` double NOT NULL DEFAULT '0',
  `longitude` double NOT NULL DEFAULT '0',
  `flag` int(11) DEFAULT NULL,
  `altitude` double NOT NULL DEFAULT '0',
  `passeddate` varchar(255) DEFAULT NULL,
  `gpsdate` date ,
  `gpstime` time ,
  `gpsdatetime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`directory`,`latitude`,`longitude`,`gpsdate`,`gpstime`,`altitude`)
);

CREATE TABLE `labels` (
  `directory` varchar(10) NOT NULL DEFAULT '',
  `starttime` datetime ,  
  `endtime` datetime ,
  `transportationmode` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`directory`,`starttime`,`endtime`,`transportationmode`)
);

INSERT INTO labels (directory, starttime, endtime, transportationmode) 
VALUES (010, '2007-06-26 11:32:29', '2007-06-26 11:40:29','bus'), 
(010, '2008-03-28 14:52:54', '2008-03-28 15:59:59', 'train'),
(010, '2008-03-29 01:27:50', '2008-03-29 15:59:59', 'train'),
(010, '2008-03-31 16:00:08', '2008-03-31 16:09:01', 'taxi'),
(010, '2008-04-01 00:48:32', '2008-04-01 00:59:23', 'taxi');

INSERT INTO plt_distinct (directory, latitude, longitude, flag, altitude, 
passeddate,gpsdate, gpstime, gpsdatetime)
VALUES (010, 18.254638, 109.500127, 0, 26, 39347.6212037037, '2007-09-22', 
'14:54:32', '2007-09-22 14:54:32'),
(010, 18.254665, 109.500092, 0, 36, 39347.6212268519, '2007-09-22', 
'14:54:34', '2007-09-22 14:54:34'),
(010, 18.254925, 109.499998, 0, 44, 39347.6213078704, '2007-09-22', 
'14:54:41', '2007-09-22 14:54:41'),
(010, 18.255035, 109.499972, 0, 38, 39347.6213310185, '2007-09-22',
'14:54:43', '2007-09-22 14:54:43');

Then I write:

SELECT COUNT(*), CASE( WHEN transportationmode='bus' THEN 'bus', 
                       WHEN transportationmode='car' OR transportationmode='taxi' THEN 'car', 
                       WHEN transportationmode='train' THEN 'train') 
              END AS mode 
FROM plt_distinct JOIN labels USING(directory);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN transportationmode='bus' THEN 'bus', WHEN transportationmode='car' OR trans' at line 1

EDIT

I produce a db-fiddle here with sample data.

Upvotes: 1

Views: 114

Answers (2)

RusArtM
RusArtM

Reputation: 1300

Maybe you need something like this:

SELECT mode, COUNT(*)
FROM (
    SELECT 
        CASE transportationmode
            WHEN 'taxi' THEN 'car'
            ELSE transportationmode
        END AS mode 
    FROM plt_distinct 
    JOIN labels USING(directory)
) t1
GROUP BY mode;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You just need fix some syntax and use group by . . . and MySQL allows you to use a column alias:

SELECT COUNT(*),
       (CASE WHEN l.transportationmode IN ('bus', 'train') THEN l.transportationmode
             WHEN l.transportationmode IN ('car', 'taxi') THEN 'car' 
        END) AS mode 
FROM plt_distinct d JOIN 
     labels l
     USING (directory)
GROUP BY mode;

Here is a db<>fiddle.

Notes:

  • Your CASE has extraneous commas and parentheses which this fixes.
  • Although your basic logic for the CASE, IN is simpler than chaining =/ORs together -- and often has better performance.
  • This simplifies the number of CASE conditions.
  • All column references are qualified, meaning that the table is part of the name.

Upvotes: 1

Related Questions