Count rows with specific value over multiple rows

Question

Its very hard for to set a proper title, because I dont know how I describe my problem.

I have a table like this:

dlID | dl_seID | dlEpisode | dlFlag
___________________________________
1    | 1       | 1         | 0
2    | 1       | 2         | 1
3    | 1       | 3         | 1
4    | 2       | 1         | 1
5    | 2       | 2         | 0
6    | 3       | 1         | 0

What i want is a select query where I get something like this:

dlID | dl_seID | dlEpisode | dlFlag | dlFlagCount
_________________________________________________
1    | 1       | 1         | 0      | 2
2    | 1       | 2         | 1      | 2
3    | 1       | 3         | 1      | 2
4    | 2       | 1         | 1      | 1
5    | 2       | 2         | 0      | 1
6    | 3       | 1         | 0      | 0

dlFlagCount shoud be a counter of dlFlag = 1 where dl_seID = dl_seID.

Second try: I need a value where I see how many Flags have the value 1 with the same dl_seID.

Is that possible?

I hope you guys know what I want^^

Regards

Jorge Campos · Accepted Answer · 2013-11-19 13:30:23Z

1

Try this:

select 
    a.*,
    ifnull(b.ctflags,0)
from 
    tablea a left join
     ( select dl_seID, count(dlFlag) ctflags 
         from tablea 
        where dlFlag=1
       group by dl_seID ) b on (a.dl_seID = b.dl_seID)

The left join is just to get the registry with 0 flags

See the fiddle: http://sqlfiddle.com/#!2/ef9b0/5

EDIT: As op requested some explanation, here it goes:

What you asked is to count the amount of flags by the dl_seID and to do that you need to do this you separeta your problems, first you get the count for the dl_seID by flags, this is this subquery:

   select dl_seID, count(dlFlag) ctflags 
     from tablea 
    where dlFlag=1
   group by dl_seID

This became a 'separe table' or a new group of data, whatever you wanna call it. Then you have to join this with your original data (from your table) like the query for answer.

The left join part is because maybe there are some data that wont complain with where dlFlag=1 therefore if you want to get then as 0 you have to bring all values from table that exists or not on our created subgroup. And this ifnull(b.ctflags,0) is for theese data data exists on your table but has no flags (for your problem). If you use just b.ctflags it will bring null.

edited Nov 19, 2013 at 13:30

answered Nov 19, 2013 at 13:14

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bluesheep Over a year ago

Thanks for the fast answer! Its works. Trying to understand your answer in the next few days ;)

Jorge Campos Over a year ago

There is not much to understand, I will update the answer with a explanation.

bluesheep Over a year ago

Dont knew the ifnull function. You need that if all flags of a specifiv dl_seID are 0.

Strawberry · Accepted Answer · 2013-11-19 13:15:01Z

0

SELECT x.*
     , COALESCE(y.flagcount,0) flagcount
  FROM my_table x
  LEFT
  JOIN 
     ( SELECT seID
            , COUNT(*) flagcount
         FROM my_table 
        WHERE flag = 1 
        GROUP 
           BY seid
     ) y
    ON y.seid = x.seid;

answered Nov 19, 2013 at 13:15

Strawberry

34k14 gold badges43 silver badges58 bronze badges

2 Comments

bluesheep Over a year ago

Thanks for the fast answer! Its works. Trying to understand your answer in the next few days too ;)

Strawberry Over a year ago

It's identical to the accepted answer, so if you understand one, then you understand the other!

Collectives™ on Stack Overflow

Count rows with specific value over multiple rows

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related