Getting Number of Active Product in a Category Magento 2 MYSQL

Getting Number of Active Product in a Category Magento 2 MYSQL.txt

### Getting Status Attribute ID
SELECT `attribute_id` FROM eav_attribute where entity_type_id = '4' AND attribute_code = 'status'

### Getting Visibility Attribute ID
SELECT `attribute_id` FROM eav_attribute where entity_type_id = 4 AND attribute_code = 'visibility'

### MySQL Query for Getting Count
SELECT
	count(CCP.category_id) AS total_products
FROM
	catalog_category_product AS CCP
	INNER JOIN cataloginventory_stock_item AS CSI ON (CCP.product_id = CSI.product_id)
		AND(CSI.is_in_stock = 1)
		INNER JOIN catalog_product_entity_int AS CPEI ON CCP.product_id = CPEI.entity_id
			AND(CPEI.attribute_id = {status_attribute_id})
			AND(CPEI.value = 1)
		LEFT JOIN catalog_product_entity_int AS CPEI2 ON CCP.product_id = CPEI2.entity_id
			AND(CPEI2.attribute_id = {visibility_attribute_id})
			AND(CPEI2.value = 4)
	WHERE
		CCP.category_id = category_id
    
    
### MySQL Query for Getting Field
SELECT
	CCP.category_id,
	CCP.product_id,
	CSI.is_in_stock,
	CPEI.value AS status,
	CPEI2.value AS visibility
FROM
	catalog_category_product AS CCP
	INNER JOIN cataloginventory_stock_item AS CSI ON (CCP.product_id = CSI.product_id)
		AND(CSI.is_in_stock = 1)
		INNER JOIN catalog_product_entity_int AS CPEI ON CCP.product_id = CPEI.entity_id
			AND(CPEI.attribute_id = {status_attribute_id})
			AND(CPEI.value = 1)
		LEFT JOIN catalog_product_entity_int AS CPEI2 ON CCP.product_id = CPEI2.entity_id
			AND(CPEI2.attribute_id = {visibility_attribute_id})
			AND(CPEI2.value = 4)
	WHERE
		CCP.category_id = {category_id}