
Untitled
By: a guest on
Apr 28th, 2012 | syntax:
None | size: 1.26 KB | hits: 15 | expires: Never
Reading evaluated data from MySQL database
ID
status (can contain values 0, 1, 2)
timepstamp
text
note
owner
number of entries
number of entries where status=0
number of entries where status=1
number of entries where status=2
number of entries where LENGTH(note)>0
minimum timestamp
maximum timestamp
SELECT status, timestamp, LENGTH(note)>0 WHERE owner="name";
SELECT
MIN(timestamp) AS mintime,
MAX(timestamp) AS maxtime,
COUNT(*) AS number,
...
WHERE owner="name"
COUNT(WHERE status=0) AS inactive
COUNT(IF(status=1)) AS active
...
SELECT
COUNT(*) AS total,
SUM(IF(status=0, 1, 0)) AS stat0,
SUM(IF(status=1, 1, 0)) AS stat1,
SUM(IF(status=2, 1, 0)) AS stat2,
SUM(IF(LENGTH(note)>0, 1, 0)) AS notes,
MIN(timestamp) AS mintime,
MAX(timestamp) AS maxtime
FROM tbl_name
WHERE owner="name"
GROUP BY owner
SELECT
MIN(`timestamp`) AS `mintime`,
MAX(`timestamp`) AS `maxtime`,
COUNT(`ID`) AS `number`,
(SELECT COUNT(`ID`) FROM `Table` WHERE `owner` = 'owner' AND `status` = 0) AS `inactive`,
(SELECT COUNT(`ID`) FROM `Table` WHERE `owner` = 'owner' AND `status` = 1) AS `active`,
(SELECT COUNT(`ID`) FROM `Table` WHERE `owner` = 'owner' AND LENGTH(`note`)>0) AS `longentries`
FROM `Table`
WHERE `owner` = 'name'