count of two values in a joined table?

Spudhead · #1 (**permalink**) 01-04-10, 08:16

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?

r937 · #2 (**permalink**) 01-04-10, 08:32

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

Spudhead · #3 (**permalink**) 01-04-10, 09:56

That's the one - thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech