Conditional Counts

Okay. I’m trying to think of the best way to design and query this.

Table: blueprints
blueprintID (int)
categoryID (FK:categories, int)
(+unrelated fields)

Table: blueprintowners
blueprintID (FK:blueprints, int)
ownerID (FK:owners, int)
type (bit)

Query Logic:
Select Count(blueprintID), count(type where type=1), count(type where type=0) from … where ownerID = somevalue group by categoryID

(Note: Count(blueprintID) != count(type where type=1) + count(type where type=0), because there may be entries in the blueprints table with no match in blueprintowners for the given ownerID (or at all, for that matter).

That was in the query logic. How to extract the number of blueprintowned.type = 1’s, blueprintowned.type = 0’s, and total count of blueprints in each category for a given ownerID.

Just one of those things where I had to come at the logic laterally (using the SUM), and then figure out why SUM wasnt working correctly.

I’m glad you solved your problem, because you forgot to tell us exactly what you were trying to achieve :wink:

Okay… apparantly BIT stores the values as chr(0) and cha(1), which causes them to not respond to SUM correctly. changed it to TINYINT and it works correctly now.

(SUM = count of type 1, COUNT = total, count of type 0 = COUNT - SUM)

I tried…

SELECT
categories.groupName,
Sum(blueprintowners.type),
Count(blueprintowners.type)
FROM
blueprints
Inner Join categories ON categories.categoryID = blueprints.categoryID
Left Join blueprintowners ON blueprintowners.blueprintTypeID= blueprints.blueprintTypeID
GROUP BY
blueprints.categoryID

But for some reason SUM and COUNT are returning the same value, even though some of the values of type are 0.