Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: Count based on two different states for same field

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT storeid, printed, Count(*)
       FROM daTable
       GROUP BY storeid, printed
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2004
    Posts
    6
    @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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •