Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: count of two values in a joined table?

    I'm not sure what I need to do here. I think it's probably simple, but my brain's stopped working.

    With the following two tables:

    tblBasket
    idBasket INT PK
    basketDate date

    tblBasketContents
    idBasketContents INT PK
    idBasket INT FK
    isFree BIT

    I want to generate a query that shows a list of tblBasket entries.

    Each row in the query resultset should show the basket date, a count of items in that Basket where isFree is 1, and a count of items where isFree is 0.

    Do I have to join the table to itself, or does it involve some sort of case statement, or is there another answer?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT b.idBasket
         , b.basketDate
         , COUNT(CASE WHEN bc.isFree = 0 THEN 'count me' END) AS not_free
         , COUNT(CASE WHEN bc.isFree = 1 THEN 'no, count me' END) AS free
      FROM tblBasket AS b
    LEFT OUTER
      JOIN tblBasketContents AS bc
        ON bc.idBasket = b.idBasket
    GROUP
        BY b.idBasket
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    That's the one - thank you

Posting Permissions

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