Syntax of query?

#1

Hi,

I have a simple table with the following data:
mysql> select * from mytable;
+---------------------+------+

Quote:

time_iso | type |
+---------------------+------+
2011-08-04 00:31:19 | S |
2011-08-04 01:05:07 | B |
2011-08-04 08:49:07 | B |
2011-08-04 20:22:55 | V |
2011-08-05 00:22:14 | S |
2011-08-05 01:15:01 | V |
+---------------------+------+

6 rows in set (0.00 sec)

I would like to get a -summary- result from a query (grouped by
date) in the following way:
+---------------------+---+---+---+-----+

Quote:

time_iso | S | B | V | tot |
+---------------------+---+---+---+-----+
2011-08-04 00:31:19 | 1 | 2 | 1 | 4 |
2011-08-05 00:22:14 | 1 | | 1 | 2 |
+---------------------+---+---+---+-----+

2 rows in set (0.00 sec)

Is that at all possible to do (in one query even)?
If yes, any help on how the query should be constructed is
appreciated...

//Queux

Quote:

DROP DATABASE if exists testdb;
CREATE DATABASE testdb CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE testdb;

CREATE TABLE `mytable` (
time_iso TIMESTAMP NOT NULL DEFAULT 0,
type text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `mytable` VALUES ('2011-08-04 00:31:19', 'S');
INSERT INTO `mytable` VALUES ('2011-08-04 01:05:07', 'B');
INSERT INTO `mytable` VALUES ('2011-08-04 08:49:07', 'B');
INSERT INTO `mytable` VALUES ('2011-08-04 20:22:55', 'V');
INSERT INTO `mytable` VALUES ('2011-08-05 00:22:14', 'S');
INSERT INTO `mytable` VALUES ('2011-08-05 01:15:01', 'V');

#2

It's a simple pivot query. In MySQL, there are a variety of methods a couple of which I'll illustrate below, or you can just handle the display logic at the application level..

SELECT MIN(time_iso) time_iso
, SUM(type = 'S') S
, SUM(CASE WHEN type = 'B' THEN 1 ELSE 0 END) B -- standard method
, COUNT(CASE WHEN type = 'V' THEN 1 END) V
, COUNT(type) tot
FROM mytable
GROUP
BY DATE(time_iso);

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Syntax of query?

comp.databases.mysql comp.databases.mysql

Syntax of query? - 08-05-2011 , 05:08 PM

Re: Syntax of query? - 08-10-2011 , 03:22 AM