dbTalk Databases Forums  

How to construct query?

Go Back dbTalk Databases Forums Databases comp.databases.mysql How to construct query?

comp.databases.mysql comp.databases.mysql


Discuss How to construct query? in the comp.databases.mysql forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
queux@hotmail.com
 
Posts: n/a

Default How to construct query? - 08-05-2011 , 02:49 PM





Hi,

I have a simple table with some data looking like this:
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)

Notice that the data shown is from two separate dates. 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)? If it is, any help on
how the
query should be constructed is much welcome...

// 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');

Reply With Quote
  #2  
Old   
Luuk
 
Posts: n/a

Default Re: How to construct query? - 08-05-2011 , 03:12 PM





On 05-08-2011 21:49, queux (AT) hotmail (DOT) com wrote:
Quote:
Hi,

I have a simple table with some data looking like this:
mysql> select * from mytable;
+---------------------+------+
| 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)

Notice that the data shown is from two separate dates. I would like to
get
a "summary" result from a query (grouped by date) in the following
way:
+---------------------+---+---+---+-----+
| 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)? If it is, any help on
how the
query should be constructed is much welcome...

// Queux


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');
select date(time_iso), group_concat(type) from mytable group by
date(time_iso);

will give you:
+----------------+--------------------+
Quote:
date(time_iso) | group_concat(type) |
+----------------+--------------------+
2011-08-04 | S,B,B,V |
2011-08-05 | S,V |
+----------------+--------------------+
2 rows in set (0.00 sec)


select
d,
sum(IF(type='S',C,0)) S,
sum(IF(type='B',C,0)) B,
sum(IF(type='V',1,0)) V,
sum(C) tot
from (select
date(time_iso) d,
type,
count(type) C
from mytable
group by date(time_iso), type)x
group by d;


gives:
+------------+------+------+------+------+
Quote:
d | S | B | V | tot |
+------------+------+------+------+------+
2011-08-04 | 1 | 2 | 1 | 4 |
2011-08-05 | 1 | 0 | 1 | 2 |
+------------+------+------+------+------+

--
Luuk

Reply With Quote
  #3  
Old   
Queux NoID
 
Posts: n/a

Default Re: How to construct query? - 08-05-2011 , 05:37 PM


Now that was a quick reply. Impressive. Thanks a lot for helping me Luuk.
I appreciate it.

// Queux

P.S
Sorry for the doublepost but it looked to me like the first one never made
it.


"Luuk" skrev i meddelandet
news:4e3c4e95$0$23904$e4fe514c (AT) news2 (DOT) news.xs4all.nl...

On 05-08-2011 21:49, queux (AT) hotmail (DOT) com wrote:
Quote:
Hi,

I have a simple table with some data looking like this:
mysql> select * from mytable;
+---------------------+------+
| 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)

Notice that the data shown is from two separate dates. I would like to
get
a "summary" result from a query (grouped by date) in the following
way:
+---------------------+---+---+---+-----+
| 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)? If it is, any help on
how the
query should be constructed is much welcome...

// Queux


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');
select date(time_iso), group_concat(type) from mytable group by
date(time_iso);

will give you:
+----------------+--------------------+
Quote:
date(time_iso) | group_concat(type) |
+----------------+--------------------+
2011-08-04 | S,B,B,V |
2011-08-05 | S,V |
+----------------+--------------------+
2 rows in set (0.00 sec)


select
d,
sum(IF(type='S',C,0)) S,
sum(IF(type='B',C,0)) B,
sum(IF(type='V',1,0)) V,
sum(C) tot
from (select
date(time_iso) d,
type,
count(type) C
from mytable
group by date(time_iso), type)x
group by d;


gives:
+------------+------+------+------+------+
Quote:
d | S | B | V | tot |
+------------+------+------+------+------+
2011-08-04 | 1 | 2 | 1 | 4 |
2011-08-05 | 1 | 0 | 1 | 2 |
+------------+------+------+------+------+

--
Luuk

Reply With Quote
  #4  
Old   
Axel Schwenke
 
Posts: n/a

Default Re: How to construct query? - 08-06-2011 , 04:19 AM


Luuk <Luuk (AT) invalid (DOT) lan> wrote:
Quote:
select
d,
sum(IF(type='S',C,0)) S,
sum(IF(type='B',C,0)) B,
sum(IF(type='V',1,0)) V,
sum(C) tot
from (select
date(time_iso) d,
type,
count(type) C
from mytable
group by date(time_iso), type)x
group by d;
Why so complicated?

SELECT time_iso,
SUM(IF(type='S', 1, 0)) AS S,
SUM(IF(type='B', 1, 0)) AS B,
SUM(IF(type='V', 1, 0)) AS V,
COUNT(*) AS tot
FROM mytable
GROUP BY DATE(time_iso);


XL

Reply With Quote
  #5  
Old   
Luuk
 
Posts: n/a

Default Re: How to construct query? - 08-06-2011 , 04:51 AM


On 06-08-2011 11:19, Axel Schwenke wrote:
Quote:
Luuk <Luuk (AT) invalid (DOT) lan> wrote:

select
d,
sum(IF(type='S',C,0)) S,
sum(IF(type='B',C,0)) B,
sum(IF(type='V',1,0)) V,
sum(C) tot
from (select
date(time_iso) d,
type,
count(type) C
from mytable
group by date(time_iso), type)x
group by d;

Why so complicated?

SELECT time_iso,
SUM(IF(type='S', 1, 0)) AS S,
SUM(IF(type='B', 1, 0)) AS B,
SUM(IF(type='V', 1, 0)) AS V,
COUNT(*) AS tot
FROM mytable
GROUP BY DATE(time_iso);


XL
When i read the original questionn, my first thought was....
GROUP_CONCAT(....

But i should look into second thoughts more often

--
Luuk

Reply With Quote
  #6  
Old   
Luuk
 
Posts: n/a

Default Re: How to construct query? - 08-06-2011 , 05:01 AM


On 06-08-2011 11:51, Luuk wrote:
Quote:
On 06-08-2011 11:19, Axel Schwenke wrote:
Luuk <Luuk (AT) invalid (DOT) lan> wrote:

select
d,
sum(IF(type='S',C,0)) S,
sum(IF(type='B',C,0)) B,
sum(IF(type='V',1,0)) V,
sum(C) tot
from (select
date(time_iso) d,
type,
count(type) C
from mytable
group by date(time_iso), type)x
group by d;

Why so complicated?

SELECT time_iso,
SUM(IF(type='S', 1, 0)) AS S,
SUM(IF(type='B', 1, 0)) AS B,
SUM(IF(type='V', 1, 0)) AS V,
COUNT(*) AS tot
FROM mytable
GROUP BY DATE(time_iso);


XL

When i read the original questionn, my first thought was....
GROUP_CONCAT(....

But i should look into second thoughts more often

I was not sure if S,B and V where the only type's that existed.

But its hard to make such a query without limiting 'type' to only these
3 values.

The best i can thing of is:
select x.D, group_concat(C) G
from (select date(time_iso) D, concat(type , count(type)) C
from mytable
group by date(time_iso),type)x
group by x.D;


+------------+----------+
Quote:
D | G |
+------------+----------+
2011-08-04 | B2,S1,V1 |
2011-08-05 | S1,V1 |
+------------+----------+


--
Luuk

Reply With Quote
  #7  
Old   
Axel Schwenke
 
Posts: n/a

Default Re: How to construct query? - 08-06-2011 , 05:50 AM


Luuk <Luuk (AT) invalid (DOT) lan> wrote:
Quote:
On 06-08-2011 11:51, Luuk wrote:
On 06-08-2011 11:19, Axel Schwenke wrote:

SELECT time_iso,
SUM(IF(type='S', 1, 0)) AS S,
SUM(IF(type='B', 1, 0)) AS B,
SUM(IF(type='V', 1, 0)) AS V,
COUNT(*) AS tot
FROM mytable
GROUP BY DATE(time_iso);

I was not sure if S,B and V where the only type's that existed.
Neither was I. But it's not specified so I chose the easiest solution
that matches the required result.

Quote:
But its hard to make such a query without limiting 'type' to only these
3 values.
If you want the `tot` column to be the exact sum of S+B+V, then add

WHERE type IN ('S', 'B', 'V')


And just to mention it: SELECT time_iso ... GROUP BY DATE(time_iso)
works in MySQL only; because `time_iso` is neither an aggregate nor
in the GROUP BY list. MySQL will chose a (more or less) random value
for time_iso from the group. Other RDBMS will reject this query.
Again I wrote the query like so because it's consistent with the
requirements (though not completely specified).


XL

Reply With Quote
  #8  
Old   
Luuk
 
Posts: n/a

Default Re: How to construct query? - 08-06-2011 , 08:43 AM


On 06-08-2011 12:50, Axel Schwenke wrote:
Quote:
Luuk <Luuk (AT) invalid (DOT) lan> wrote:
On 06-08-2011 11:51, Luuk wrote:
On 06-08-2011 11:19, Axel Schwenke wrote:

SELECT time_iso,
SUM(IF(type='S', 1, 0)) AS S,
SUM(IF(type='B', 1, 0)) AS B,
SUM(IF(type='V', 1, 0)) AS V,
COUNT(*) AS tot
FROM mytable
GROUP BY DATE(time_iso);

I was not sure if S,B and V where the only type's that existed.

Neither was I. But it's not specified so I chose the easiest solution
that matches the required result.

But its hard to make such a query without limiting 'type' to only these
3 values.

If you want the `tot` column to be the exact sum of S+B+V, then add

WHERE type IN ('S', 'B', 'V')
This should indeed be added,
or a check if this total equals the sum of the 3 numbers before it

Quote:

And just to mention it: SELECT time_iso ... GROUP BY DATE(time_iso)
works in MySQL only; because `time_iso` is neither an aggregate nor
in the GROUP BY list. MySQL will chose a (more or less) random value
for time_iso from the group. Other RDBMS will reject this query.
Again I wrote the query like so because it's consistent with the
requirements (though not completely specified).
Thes why i did:
SELECT date(time_iso)
.....

GROUP BY date(time_iso);

This should work in other DBMSs too.


--
Luuk

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top