COUNT NUMBER OF ITEM IN TABLE 2

[pelagic]

hi All,
I have Access table named tblCatDog, which included the following:
SUB CATEGORY PRICE BEFORE PRICE AFTER
CAT 100 200
CAT 105 200
CAT 150 200
CAT 250 200
CAT 300 200
DOG 400 500
DOG 450 500
DOG 550 500
How would I count number of under price (less then price after) and over price (more then price after)?
The result would be:
CAT under price would be 3
CAT over price would be 2
DOG under price would be 2
DOG over price would be 1

thanks for your time,
regards

 

[PHV]

Something like this ?

SELECT [SUB CATEGORY],Count(*)
  FROM tblCatDog
 WHERE [PRICE BEFORE] < [PRICE AFTER]
 GROUP BY [SUB CATEGORY]
UNION
SELECT [SUB CATEGORY],-Count(*)
  FROM tblCatDog
 WHERE [PRICE BEFORE] > [PRICE AFTER]
 GROUP BY [SUB CATEGORY]
ORDER BY 1,2

Hope This Help
PH.

 

[dnoeth]

select
sub_category,
price_type,
count(*)
from
(
select
sub_category,
case
when price_before <= price_after then 'under price'
else 'over price'
end as price_type
from tblCatDog
) dt
group by
sub_category,
price_type


Dieter

 

[JarlH]

Isn't the case when PRICE AFTER is equal to PRICE BEFORE of interrest?

I suggest the following SQL statement:

SELECT sub_category,
SUM(CASE
WHEN price_before = price_after THEN 1
ELSE 0 END) AS same_price,
SUM(CASE
WHEN price_before > price_after THEN 1
ELSE 0 END) AS under_price,
SUM(CASE
WHEN price_before < price_after THEN 1
ELSE 0 END) AS over_price
FROM tblCatDog
GROUP BY sub_category