count how many records are true

  • Thread starter Thread starter erwt
  • Start date Start date
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
 
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?
 
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
 
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
 
Back
Top