| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-25-10, 03:20
|
|
Registered User
|
|
Join Date: Nov 2003
Location: Sinapore
Posts: 187
|
|
|
Division of grouped items
|
|
Hi everyone,
I've some data as follows:
PHP Code:
+---------------------+---------+
| date | flag |
+---------------------+---------+
| 2010-03-23 16:06:47 | 1 |
| 2010-03-23 16:06:47 | 1 |
| 2010-03-23 16:06:47 | 1 |
| 2010-03-23 16:06:47 | 1 |
| 2010-03-23 16:06:47 | 1 |
| 2010-03-23 16:07:46 | 1 |
| 2010-03-23 16:07:46 | 1 |
| 2010-03-23 16:07:46 | 1 |
| 2010-03-23 16:07:46 | 1 |
| 2010-03-23 16:07:46 | 0 |
| 2010-03-23 16:08:44 | 0 |
| 2010-03-23 16:08:44 | 0 |
| 2010-03-23 16:08:44 | 0 |
| 2010-03-23 16:08:44 | 0 |
| 2010-03-23 16:08:44 | 0 |
| 2010-03-23 16:10:30 | 1 |
| 2010-03-23 16:10:58 | 0 |
| 2010-03-24 08:32:56 | 0 |
| 2010-03-24 08:33:25 | 1 |
| 2010-03-24 08:33:25 | 1 |
| 2010-03-24 08:33:25 | 1 |
| 2010-03-24 08:33:25 | 1 |
| 2010-03-24 08:33:25 | 1 |
| 2010-03-24 08:33:54 | 0 |
| 2010-03-24 08:33:54 | 0 |
| 2010-03-25 14:11:38 | 1 |
| 2010-03-25 14:11:38 | 1 |
| 2010-03-25 14:11:38 | 1 |
| 2010-03-25 14:11:38 | 1 |
| 2010-03-25 14:11:38 | 1 |
| 2010-03-25 14:12:16 | 1 |
| 2010-03-26 10:07:21 | 0 |
| 2010-03-26 10:07:21 | 0 |
| 2010-03-26 10:07:21 | 0 |
| 2010-03-26 10:07:21 | 0 |
| 2010-03-26 10:07:21 | 0 |
+---------------------+---------+
I'm trying to write a query that computes the number of 1 flags over the sum of 1 and 0 flags for each date. Something like this:
PHP Code:
+-------------+-----------+---------
| date | 1's | 0's | result |
+-------------+-----+-----+---------
| 2010-03-23 | 10 | 7 | 0.58 |
| 2010-03-24 | 5 | 3 | 0.625 |
| 2010-03-25 | 6 | 0 | 1.00 |
| 2010-03-26 | 0 | 5 | 0.00 |
+-------------+-----+-----|--------+
Thank you in anticipation 
Edited:
This is what I've tried:
PHP Code:
SELECT done1, count1/(count1+count2)
FROM
(SELECT DATE(done) AS done1, COUNT(*) AS count1
FROM data WHERE flag=1 GROUP BY DATE(done) ASC) AS t1,
(SELECT DATE(done) AS done2, COUNT(*) AS count2
FROM data WHERE flag=0 GROUP BY DATE(done) ASC) AS t2
WHERE done1=done2
But it works only if both 1's and 0's exist on the common date.
|
Last edited by pearl2; 03-25-10 at 05:21.
|
03-25-10, 05:48
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Code:
SELECT d, count1, count2, count1/(count1+count2) AS result
FROM ( SELECT d , SUM(c1) AS count1, SUM(c2) AS count2
FROM (
( SELECT DATE(done) AS d, COUNT(*) AS c1, 0 AS c2
FROM data WHERE flag=1 GROUP BY d ) AS t1
UNION ALL
( SELECT DATE(done) AS d, 0 AS c1, COUNT(*) AS c2
FROM data WHERE flag=1 GROUP BY d ) AS t2
) AS u
GROUP BY d ) AS t
|
|
03-25-10, 06:30
|
|
Registered User
|
|
Join Date: Nov 2003
Location: Sinapore
Posts: 187
|
|
|
|
Hi r937,
I get an error when trying to run you code:
Code:
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 'AS t2
) AS u
GROUP BY d ) AS t' at line 8
I'm still trying to see if I've missed out anything...
|
|
03-25-10, 08:52
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
that's weird, why would it hiccup right there, and not near AS t1
try removing the t1 and t2 aliases
|
|
03-25-10, 08:56
|
|
Registered User
|
|
Join Date: Nov 2003
Location: Sinapore
Posts: 187
|
|
Wonderful!
Though I've been writing SQL queries for some time now, I would never be able to craft something like that.
Thank you so much 
|
|
03-27-10, 05:44
|
|
Registered User
|
|
Join Date: Mar 2006
Posts: 55
|
|
Hi,
I am just a passer-by but just puzzled out a different code. This yields the same results. So I am wondering if someone could evaluate this in terms of execution costs.
Regards,
ik
Code:
select d, sum(ones), sum(zeros), sum(ones) / (sum(ones) + sum(zeros)) as results
from (select left(date,10) d
, case when flag = 1 then 1 else 0 end as ones
, case when flag = 0 then 1 else 0 end as zeros
from flags) tb1
group by d;
|
Last edited by Ikviens; 03-27-10 at 05:52.
|
03-27-10, 08:30
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
that's a nice alternative, Ikviens, except you cannot apply LEFT to a datetime column
okay, maybe you can in MySQL, but you shouldn't, as it relies on an implicit conversion from date to string
|
|
03-27-10, 08:47
|
|
Registered User
|
|
Join Date: Mar 2006
Posts: 55
|
|
I had checked my answer before I posted it here, so I knew that MySQL does not raise an eyebrow for this implicit conversion.
Still, Rudy, it should be avoided whenever possible. So here are two possible re-writes:
DATE(date) --Oh, an abuse of reserved keywords...
LEFT(CAST(date AS CHAR), 10)
|
|
03-27-10, 09:04
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
yes, i prefer DATE() for this
and you're right, `date` is a poor column name
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 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
HTML code is Off
|
|
|
|
|
dBforums
>
Division of grouped items
