Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count of two types of values in same field in MS-Access

I have this table customerDetail, in which there's a field c_type, in which "a" represents "active" and "d" represents "not-active". Now I have to find the count of both of them in same query.
I used these but no result.

SELECT Count(c_type) AS Active, Count(c_type) AS Not_Active  
FROM customerDetail  
WHERE c_type="a" OR c_type="d"

of course I know it obviously looks dirty, but I have also tried this, but this didn't worked either-

SELECT
    Count(customerDetail.c_type) AS Active,
    Count(customerDetail_1.c_type) AS Not_Active  
FROM customerDetail INNER JOIN customerDetail AS customerDetail_1  
ON customerDetail.Id=customerDetail_1.Id  
WHERE (customerDetail.c_type="a") AND (customerDetail_1.c_type="d")

But again it wasn't helpful either, so can anyone please tell me how am I supposed to know the count of both active and non-active in same query?

like image 951
Razort4x Avatar asked Feb 03 '26 13:02

Razort4x


2 Answers

select c_type, count(*)
from customer_detail
group by c_type
like image 62
Mike Sherrill 'Cat Recall' Avatar answered Feb 06 '26 04:02

Mike Sherrill 'Cat Recall'


SELECT
  SUM(IIF(c_type = "a", 1, 0)) AS Active,
  SUM(IIF(c_type = "d", 1, 0)) AS Not_Active,
FROM customerDetail
WHERE c_type IN ("a", "d")

That was for MS Access.

Somehow I missed the tsql tag when first saw this question. In Transact-SQL you can employ a CASE construct, which can be said of as a more powerful equivalent of IIF in Access:

SELECT
  SUM(CASE c_type WHEN 'a' THEN 1 ELSE 0 END) AS Active,
  SUM(CASE c_type WHEN 'd' THEN 1 ELSE 0 END) AS Not_Active,
FROM customerDetail
WHERE c_type IN ('a', 'd')

Actually, in T-SQL I would use COUNT instead of SUM, like this:

SELECT
  COUNT(CASE c_type WHEN 'a' THEN 1 END) AS Active,
  COUNT(CASE c_type WHEN 'd' THEN 1 END) AS Not_Active,
FROM customerDetail
WHERE c_type IN ('a', 'd')

Here 1 in each CASE expression can be replaced by anything as long as it is not NULL (NULLs are not counted). If the ELSE part is omitted, like in the query above, ELSE NULL is implied.

like image 38
Andriy M Avatar answered Feb 06 '26 05:02

Andriy M



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!