2

I have a query with sum aggregation function :

SELECT sum(case when result=1 then 1 when result=2 then 0)as final_result From results

I want to change this part when result=2 then 0 to something like that when result=2 then final_result equals zero

Is it possible to do this ? or there is another way for that?

Basel
  • 1,305
  • 7
  • 25
  • 34
  • In the comments somewhere you say _"with the values `1,1,1,1,2,1` the `final_result` as I want should be 1"_. Could you explain why the `final_result` should be `1`? Because from your question it seems you want `final_result=0` because there exists a row where `result=2`. – asontu Feb 24 '15 at 08:42
  • @funkwurm I meant th whole sum should be zero when we face 2 and it sums normally otherwise – Basel Feb 24 '15 at 08:46
  • Does my answer work for you? Or do you mean to say that when we face 2, the number goes back to 0 and starts counting again? So `1,1,1,2,1,1 -> final_result = 2` and `1,1,2,1,1,1 -> final_result = 3`? – asontu Feb 24 '15 at 08:49
  • @funkwurm yes it goes down to zero and start counting again – Basel Feb 24 '15 at 08:53

2 Answers2

0

Try this :

SELECT CASE WHEN cnt=sum THEN sum ELSE 0 END as Final_result
FROM
(
    SELECT count(*) as cnt,
           SUM(case when result=1 then 1 else 0 end) as sum
     from results
) Temp

Working Fiddle here.

Explanation:

Inner query select the total number of records and the sum of the records. Then if those count and sum are equal that means all values are 1.

NB: Removed the checking when result=2 then 0 because, the query will select 0 for any values other than 1. So there is no need to check for result=2.

EDIT:

To find the count of 10 consecutive 1's, you can do:

SELECT SUM(CASE WHEN RN=10 THEN 1 ELSE 0 END) AS final_result
FROM
(
SELECT CASE WHEN result=1 THEN @row_number:=@row_number+1 ELSE @row_number:=0 END AS RN,
       result 
FROM results, (SELECT @row_number:=0) AS t
) Temp

Sample Fiddle here.

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • Nope! Change the 5th value to a 2 and you get `5`. [**Fiddle here**](http://www.sqlfiddle.com/#!2/4f49c/1/0). – asontu Feb 24 '15 at 08:26
  • http://www.sqlfiddle.com/#!2/4f49c/1 In this link I update the value `1,1,1,1,2,1` The `final_result` as I want should be 1 here but it shows 5 I want on each 2 value to make the whole sum zero?? – Basel Feb 24 '15 at 08:27
  • @BaselShbeb: Updated my answer. Its working now. Try with the new fiddle link. – Raging Bull Feb 24 '15 at 08:32
  • Change the `2` value to a `0` value and it gives `0` for the final result: **[fiddle](http://www.sqlfiddle.com/#!2/9a85b8/1/0)**. You're asking to have the downvote removed but your code never checks for `result=2` when that is clearly the condition the OP gave you... – asontu Feb 24 '15 at 08:36
  • @funkwurm: OP wants to get the count of result having 1. The sum function will select 0 for any value other than 1. So there is no point in specifically checking for 2. – Raging Bull Feb 24 '15 at 08:41
  • 1
    That works fine thank you. But I have a question about this snippet. is it possible to check for each 10 streaks. I mean each 10 continues 1s will add one to the sum unless we face 2 it will make the whole sum equals to zero – Basel Feb 24 '15 at 08:42
  • @BaselShbeb: Yes, we can do that using row number. But it will be a little more complicated query. – Raging Bull Feb 24 '15 at 08:48
  • is it possible to help me by a fiddle link about that , If possible ofc – Basel Feb 24 '15 at 08:51
  • @BaselShbeb: It is a complicated query. Anyway, I will try on that. And it may take a while. I will let you know as soon as I crack it. ok? – Raging Bull Feb 24 '15 at 08:53
  • @BaselShbeb: Got it !! Please find the edit part in my answer. – Raging Bull Feb 24 '15 at 09:51
0

I think you want something like this:

SELECT case max(case result when 2 then 1 else 0 end)
           when 1 then 0
           else sum(case when result=1 then 1 else 0 end)
       end as final_result
From results

Fiddle here

So what I'm doing is checking for the final-result-must-be-0 condition inside a MAX(). That way, if any of the rows meets this condition (i.e. result = 2), the maximum value will be 1. Then I can use that value to determine whether I wanna return 0 or do the SUM().

asontu
  • 4,548
  • 1
  • 21
  • 29