Mysql SUM CASE with unique IDs only -

The easiest explained by an example.

A father has children running children

How many fathers dynasty live in the race and in the total number of castes a father's descendants (winners and wins)

I can easily find the sum total of victory But sometimes a child gets more than one race so that I need only the sum to explain the winners, if the child has won, it has not been won every time.

I can not use separately to exclude from a query, so it does not work

  SUM (case when r.finish = '1' and different H. Runnersaid 1 1 ELSE 0 END) As a winner,  

this will not work too

  SUM (select DISTINCT r.runnersid runs by R WHERE r.finish = '1') Winner  

This works when I should get the total amount of victory.

  SUM (case when Rfinish = '1' THEN 1 ELSE 0 END) wins,  

here is a sqlfiddle

Let's take action from this step.

You have two pieces of information that you are seeing: who has won a race, and how many castes they are. / P>

By taking the first one, you can select a different runner. Here they have the first place to finish:

  Choose finish from where the run ends = 1;  

For the second part, you can select each sub-inspector where the first place ends, the number of rows is calculated, and for the total victory for each Group by suburban group:

  SELECT runnersid, COUNT (*) as run by the runesid = 1 group by running numwins;  

The second one is really everything you want, you do not have to do anything with that first question, but when I try to complete a task like this I used it to help in performing the thought process.

The example is here.

Edit

As you have seen, you do not really need this amount because the race represents a place in the race, you Do not want to value that value, but you want COUNT to win.

EDIT2

An additional edit based on OP requirements. Does not match the needs of the above OP, but I left it as reference to future readers. What is the OP really needed, because I understand it is the number of children of each father who has participated in the race. I will then explain the steps of my thinking process by step.

Earlier I wrote a simple question that all winning father and son pairs. I was able to use Group B to get specific winning pairs:

  Choose Father, name is fulfilled by WHERE finish = 1 group by father, name;  

Once I have done this, I have used it and it is a subquery and COUNT (*) function to get the number of winners for each father (this means I must be a group by father):

Choose Father, name (father) by nameWinningChildren (father), name ends by WHERE = 1 group, name by father, name ) By T-group by Father;

If you need a father to win the children, then you have done it. If you want to see all the Fathers, then I will write a query to choose all the ancestors, by setting our results above and substituting any values ​​where num.WinningChildren is zero, With 0

I will leave that part to a little bit more challenge to you than that, because the SQL Nerd is currently low and I can not test what I am thinking, but I have successfully taken the test of those people Was able to