MySQL Group By Outputs List

Posted on 2011-03-01
Last Modified: 2012-05-11
I have a table that looks like this:

        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

            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.

Question by:marcparillo
LVL 24

Expert Comment

ID: 35013882
This one will do:
select mid, sid, count(encode) files from Yourtable group by mid, sid, encode having encode = 0;

Assisted Solution

dtocci earned 250 total points
ID: 35014163
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;
LVL 22

Accepted Solution

Thomasian earned 250 total points
ID: 35014166
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

LVL 40

Expert Comment

ID: 35014722
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


Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now