Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Unanswered: Need urgently Mysql query for following case

    Hi All , I am new to mySQL , here is a sample data of my table :

    | Run | End Date |
    ---------------------------------
    Passed | 1/30/2014 |
    Passed | 1/30/2014 |
    Failed | 1/31/2014 |
    Passed | 1/28/2014 |
    Passed | 2/10/2014 |
    Failed | 2/12/2014 |
    Passed | 2/12/2014 |
    Passed | 12/9/2013 |
    Passed | 12/10/2013 |
    Failed | 12/18/2013 |
    Not Run | |
    Passed | 12/18/2013 |
    Passed | 12/17/2013 |
    Passed | 12/17/2013 |
    Passed | 12/16/2013 |
    Passed | 12/16/2013 |
    ------------------------------------

    I want an mySQL query which will give me out put data containing :


    Number of pass | Number of Fail | Not run | Date |
    ----------------------------------------------------------
    10 | 2 | 1 | 12/16/2013 |
    ----------------------------------------------------------


    ie . [ Pass / fail /not run ] for each day

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    based on the sample data supplied I can't see any way to get to the specified resultset

    16/12/2013 isn't the earliest or latest date
    theres only 2 rows for 16/12/2013 so you cant get the required result by summarising on date
    the 'not run' row has no date, so I don't see how you can summarise on a date
    the numbers don't tally with any mechanism I can see. lets assume you want to display the date from the last row (which could be dangerous unless you specify a sort order) there are 16 rows of data, but 13 in your summary.

    I would expect your eventual SQL will include a GROUP BY clause and a CASE or IF sub clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2012
    Posts
    1
    Your date column is in a string format, not a date format. Causes problems when trying to sort. I named my table "test_table". Change this to the correct table name.

    Code:
    SELECT 
    	COUNT(case when Run='Passed' then 1 else null end) as Passed,
    	COUNT(case when Run='Failed' then 1 else null end) as Failed,
    	COUNT(case when Run='Not Run' then 1 else null end) as NotRun,
    	`End Date` as Date
     FROM test_table
    GROUP BY `End Date`
    WITH ROLLUP
    Andy
    Attached Thumbnails Attached Thumbnails query_result.jpg  

Tags for this Thread

Posting Permissions

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