Attributeset

Counting products per attribute set using SQL (Magento 2)

Mex 2. September 2026 Magento Solutions

It can be useful to count how many products belong to each attribute set. The simplest approach in Magento 2 is the following SQL query:

SELECT
    eas.attribute_set_id,
    eas.attribute_set_name,
    COUNT(cpe.entity_id) AS product_count
FROM eav_attribute_set eas
LEFT JOIN catalog_product_entity cpe
    ON cpe.attribute_set_id = eas.attribute_set_id
WHERE eas.entity_type_id = (
    SELECT entity_type_id
    FROM eav_entity_type
    WHERE entity_type_code = 'catalog_product'
)
GROUP BY
    eas.attribute_set_id,
    eas.attribute_set_name
ORDER BY
    eas.attribute_set_name;

SELECT

eas.attribute_set_id,

eas.attribute_set_name,

COUNT(cpe.entity_id) AS product_count

FROM eav_attribute_set eas

LEFT JOIN catalog_product_entity cpe

ON cpe.attribute_set_id = eas.attribute_set_id

WHERE eas.entity_type_id = (

SELECT entity_type_id

FROM eav_entity_type

WHERE entity_type_code = 'catalog_product'

)

GROUP BY

eas.attribute_set_id,

eas.attribute_set_name

ORDER BY

eas.attribute_set_name;

The result lists the number of products assigned to each attribute set.

View solution

Getting orders with items that have a certain attribute set (Magento 1)

Mex 3. März 2014 Magento Solutions

The following MySQL query returns all Magento orders that have at least one item with attribute set id 9.

/* 9 = attribute set Id */
select
	increment_id,
	created_at,
	entity_id 
from
	sales_flat_order 
where
	entity_id not in (
		SELECT
			o.entity_id      
		FROM
			sales_flat_order o      
		LEFT JOIN
			sales_flat_order_item i        
				ON o.entity_id = i.order_id      
		LEFT JOIN
			catalog_product_entity p        
				ON i.product_id = p.entity_id      
		WHERE
			p.attribute_set_id = 9
	)

/* 9 = attribute set Id */

select

increment_id,

created_at,

entity_id

from

sales_flat_order

where

entity_id not in (

SELECT

o.entity_id

FROM

sales_flat_order o

LEFT JOIN

sales_flat_order_item i

ON o.entity_id = i.order_id

LEFT JOIN

catalog_product_entity p

ON i.product_id = p.entity_id

WHERE

p.attribute_set_id = 9

)

View solution

Getting the attribute set name of a product (Magento 1)

Mex 20. Januar 2014 Magento Solutions

Use the following code to get a products attribute set name.

$attributeSetModel = Mage::getModel("eav/entity_attribute_set");
$attributeSetModel->load($product->getAttributeSetId());
$attributeSetName  = $attributeSetModel->getAttributeSetName();

$attributeSetModel = Mage::getModel("eav/entity_attribute_set");

$attributeSetModel->load($product->getAttributeSetId());

$attributeSetName = $attributeSetModel->getAttributeSetName();

View solution

The Magento Expert

Independent Magento developer & consultant

Counting products per attribute set using SQL (Magento 2)

Getting orders with items that have a certain attribute set (Magento 1)

Getting the attribute set name of a product (Magento 1)