MySQL Group By Outputs List

I have a table that looks like this:

+----id-----+------mid------+-----sid------+------file-----+----encode-----+
        1                 A                                       file1                0
        2                 A                                       file2                1
        3                 A                                       file3                0
        4                                      B                  file4                0
        5                                      B                  file5                0


I'm trying to create a query that will list the number of files that have an encode value of 0, grouped by the mid or sid.  So, one query would give me this result


+------mid------+-----sid------+------files-----+
            A                                         2              
                                B                     2              

I don't know what the value the 'mid' or 'sid' columns will have.  I just want a count, grouped by the files' sid or mid values.

Not exactly sure how to do this.

Thanks,
marc
LVL 3
marcparilloAsked:
Who is Participating?
 
ThomasianCommented:
If you want to return files=0:
select mid, sid, count(nullif(encode,0)) files
from tablename
group by mid, sid

Open in new window

if you don't need to return files=0:
select mid, sid, count(*) files
from tablename
where encode=0
group by mid, sid

Open in new window

0
 
jimyXCommented:
This one will do:
select mid, sid, count(encode) files from Yourtable group by mid, sid, encode having encode = 0;
0
 
dtocciCommented:
I wouldn't group by "encode" at all and I'd use a where clause first.  I think this will work:

SELECT mid, sid, count(encode) AS files FROM Yourtable WHERE encode = 0 GROUP BY mid, sid;
0
 
SharathData EngineerCommented:
If you have data like this, you will get an extra record for A,B combination with Thomasian's query. Is that what you are looking for?
mysql> select * from tab2;
+------+------+------+-------+--------+
| id   | mid  | sid  | file  | encode |
+------+------+------+-------+--------+
|    1 | A    | NULL | file1 |      0 |
|    2 | A    | NULL | file2 |      1 |
|    3 | A    | NULL | file3 |      0 |
|    4 | NULL | B    | file4 |      0 |
|    5 | NULL | B    | file5 |      0 |
|    5 | A    | NULL | file6 |      0 |
|    5 | A    | B    | file7 |      0 |
+------+------+------+-------+--------+
7 rows in set (0.00 sec)

mysql> select mid, sid, count(*) files
    ->   from tab2
    ->  where encode=0
    ->  group by mid, sid;
+------+------+-------+
| mid  | sid  | files |
+------+------+-------+
| NULL | B    |     2 |
| A    | NULL |     3 |
| A    | B    |     1 |
+------+------+-------+
3 rows in set (0.00 sec)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.