Count based on two different states for same field

datune · #1 (**permalink**) 05-31-10, 04:15

Hello,

if I have one table, with two fields, storeid and printed, and I want to select a count of all records grouped by storeid where printed=0 BUT would also like to have the count of all records per storeid where printed=1

Can this be done in a single query?

Example table:

Code:

id      storeid     printed
1           3           0
2           3           1
3           3           1
4           3           1
5           3           0
6           6           0
7           6           1
8           6           1

Expected result:

Code:

storeid     count_printed   count_not_printed
    3           3                   2
    6           2                   1

r937 · #2 (**permalink**) 05-31-10, 07:16

Code:

SELECT storeid
     , COUNT(CASE WHEN printed = 1
                  THEN 'humpty' 
                  ELSE NULL END) AS count_printed
     , COUNT(CASE WHEN printed = 0
                  THEN 'dumpty' 
                  ELSE NULL END) AS count_not_printed
  FROM daTable
GROUP
    BY storeid

Pat Phelan · #3 (**permalink**) 05-31-10, 14:05

Code:

SELECT storeid, printed, Count(*)
   FROM daTable
   GROUP BY storeid, printed

-PatP

datune · #4 (**permalink**) 06-01-10, 01:28

@r937

Thanks a lot, that is exactly what I was looking for. Even though I have been working with MySQL for over ten years, I never had this case before, and I never would have thought of what you did. Much appreciated!

Internet Services

Web Development

Digital Marketing

Consumer Tech