SQL : How to calculate limited rows and reset the counter

Question

I am dealing with an issue and need some expert advice on to achieve the problem, my sql query generates output with two columns, 1st column displays id (for e.g. abc-123 in following table) and next column displays corresponding result to the id stored in db which is pass or fail.

I need to implement, when resolution is pass it should display success attempt, in following example, abc-123 failed 1st time however def-456 passed in next attempt thus success rate is 50%, now counter should reset and go to next row where there is pass thus it should show 100%, again when code hits pass counter resets then goes next and displays 33% bec there are two fail and one pass at the end, how it can be achieved in sql? (id and resolution are column names)

**date**       **id resolution**    
 6/6/2012     abc-123   fail       50%
 6/7/2012     abc-456   pass    
 6/8/2012     abc-789   pass       100%
 6/9/2012     abc-799   fail       33%
 6/10/2012    abc-800   fail    
 6/1/2012     abc-900   pass

Thanks

Is there some order or timestamp column to indicate which item is before the next? — Holger Brandt, Jun 29 '12 at 19:48
Hi Holger, I fixed the output, id is in ascending columns, also yes there is a date column which is in ascending order. — yokoyoko, Jun 29 '12 at 19:50
Hi Holger, I have added a date column which is in ascending order. — yokoyoko, Jun 29 '12 at 19:55
maybe this link can help http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql — Nahuel Fouilleul, Jun 29 '12 at 19:56
Hi Nahuel, this link is discussing about grouping, I am not trying to group, I just need to read column and reset the counter and calculate success. — yokoyoko, Jun 29 '12 at 19:58
1) You are thinking about the problem procedurally, whereas SQL works with sets. 2) Your analysis of the data makes no sense to me. Why should the records on 6/6 and 6/7 be grouped together to compute a percent? They do not share a common id. Can there not be multiple resolutions on the same day? How would you group them for percents then? — dbenham, Jun 29 '12 at 20:18
@dbenham - You group all records sequentially up to the first pass. Each group can have many fails, but only one pass. First group = first 2 records. next group = next 1 record. final group = final 3 records. *[Although, my answer works that logic backwards. From a pass the the record immediately after the preceding pass.]* — MatBailie, Jun 29 '12 at 20:24
@dbenham The sets (or islands of data) are bound by "pass". There are also plenty of problems where "thinking procedurally" is required. If it weren't there'd be no windowing functions like `ROW_NUMBER() OVER...` and `LAG() OVER..` in Oracle and SQL Server. — Conrad Frix, Jun 29 '12 at 20:31
I can see how how the grouping is done, but it still doesn't make sense. What is the significance of the group percentage? And more importantly, how do you order the records of a single day with multiple passes and fails? I can see where it is an interesting problem to solve as described from a SQL standpoint, but I'm wondering if the resultant percents are going to be misrepresented, thus leading someone to draw the wrong conclusions. — dbenham, Jun 29 '12 at 20:41

MatBailie · Accepted Answer · 2012-06-29T20:21:03.240

3

SELECT
  *
FROM
  table
INNER JOIN
(
  SELECT
    MIN(g.id)   AS first_id,
    MAX(g.id)   AS last_id,
    COUNT(*)    AS group_size
  FROM
    table AS p
  INNER JOIN
    table AS g
      ON g.id > COALESCE(
                  (SELECT MAX(id) FROM table WHERE id < p.id AND resolution = 'pass'),
                  ''
                )
     AND g.id <= p.id
  WHERE
    p.resolution = 'pass'
  GROUP BY
    p.id
)
  AS groups
    ON  table.id >= groups.first_id
    AND table.id <= groups.last_id

edited Jun 29 '12 at 20:21

answered Jun 29 '12 at 20:05

MatBailie

83,401
18
103
137

I've assumed that your `id` column is a VARCHAR() or similar. Which is why I use `''` in the `COALESCE()` *(to include all id's)*. – MatBailie Jun 29 '12 at 20:11
@Dems : group by on resolution? – yokoyoko Jun 29 '12 at 20:22
@yokoyoko - No, `GROUP BY p.id` on the main sub-query. I've already added it to the answer :) – MatBailie Jun 29 '12 at 20:23
1

@yokoyoko - Because in that sql fiddle the table is called `table1` but you have it as `table` in some places and `table1` in others. Try this link? http://sqlfiddle.com/#!2/177cb/8/0 – MatBailie Jun 29 '12 at 20:29
@Dems : link works, so you are saying : if group_size = 2 means success rate is 50%, if its 3 then success rate is 33%? – yokoyoko Jun 29 '12 at 20:31
2

@yokoyoko - Yes. Success rate in the group = `100.0 / group_size`. And if you only want to show it on the first record in the group then you can use... `CASE WHEN table.id = groups.first_id THEN 100.0 / groups.group_size ELSE NULL END AS pass_rate,` Exactly how you use the data is up to you, but the query as is should give you eveything you need to calculate what you want where you want. http://sqlfiddle.com/#!2/6a3d5/3 – MatBailie Jun 29 '12 at 20:34

score 2 · Answer 2 · answered Jun 29 '12 at 20:16

2

There's more than one way to do it:

SELECT st.*, 
       @prev:=@counter + 1,
       @counter:= CASE 
         WHEN st.resolution = 'pass'
         THEN 0
         ELSE @counter + 1
       END c,
       CASE WHEN @counter = 0 
            THEN CONCAT(FORMAT(100/@prev, 2), '%') 
            ELSE '-' 
       END res
  FROM so_test st, (SELECT @counter:=0) sc

Here's proof of concept.

answered Jun 29 '12 at 20:16

raina77ow

103,633
15
192
229

Actually that link with grouped query helped much. The concept itself is simple: we have two variables, one (@prev) accumulates failures, another (@counter) helps the first one, but also acts like an independent trigger of a sort. ) – raina77ow Jun 29 '12 at 20:21
And yes, from my point of view these kinds of tasks are better served with some procedural approach. ) But then again, requirements differ. – raina77ow Jun 29 '12 at 20:22

SQL : How to calculate limited rows and reset the counter

2 Answers2