count how many records are true

E

erwt

I've got a table like this

ProductID Ordere
132 TRU
45 TRU
145 FALS
48 TRU
74 FALS
145 FALS
85 TRU
64 TRU
74 FALS

I want to have how many times a product is not ordered. So this mus
be the result

ProductID Ordere
145
74

I've tried GROUP BY, COUNT, COUNT(DISTINCT) but nothing works. Ca
anyone help me
 
C

Chaim

SELECT temp1.pid, count(temp1.ordered)
FROM temp1
WHERE ordered = 0
group by pid;

Why are there two records for a product? Implies that a product can be both
ordered and unordered?
 
M

Michel Walsh

Hi,

You can also consider that generated True are, in fact -1 (in Jet) , and
False are 0:


SELECT whatever
ABS(SUM(BooleanFieldOrExpression)) As CountOfTrue,
SUM(1+BooelanFieldOrExpression) As CountOfFalse,
COUNT(*)-COUNT(BooleanFieldOrExpression) As CountOfNull
FROM myTable
GROUP BY whatever


or, which is not based on particular value for true or false:


TRANSFORM COUNT(*) As theValue
SELECT whatever
FROM myTable
GROUP BY whatever
PIVOT Switch(BooleanFieldOrExpression, "CountOfTrue",
NOT BooleanFieldOrExpression, "CountOfFalse",
true, "CountOfNULL" )




Hoping it may help,
Vanderghast, Access MVP
 
E

erwt

Chaimwrote
SELECT temp1.pid, count(temp1.ordered
FROM temp
WHERE ordered =
group by pid

Why are there two records for a product? Implies that a product ca be bot
ordered and unordered

-

Chai
Click to expand...

Yes. But it's not i the table for product but in a table for ordere
products

PRODUC
I
TITL

[b:8c0be4347c]ORDER[/b:8c0be4347c
I
CUSTOMERI

[b:8c0be4347c]ORDERROW[/b:8c0be4347c
I
ORDERI
PRODUCTI
 
Top