Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: 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'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 done1count1/(count1+count2)
        
    FROM
            
    (SELECT DATE(done) AS done1COUNT(*) AS count1
                FROM data WHERE flag
    =1 GROUP BY DATE(doneASC) AS t1,
            (
    SELECT DATE(done) AS done2COUNT(*) AS count2
                FROM data WHERE flag
    =0 GROUP BY DATE(doneASC) 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 06:21.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's weird, why would it hiccup right there, and not near AS t1

    try removing the t1 and t2 aliases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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

  6. #6
    Join Date
    Mar 2006
    Posts
    56
    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 06:52.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2006
    Posts
    56
    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)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i prefer DATE() for this

    and you're right, `date` is a poor column name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •