SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 30
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 30
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 31
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 31
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 32
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 32
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 9
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 9
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 34
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 34
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 35
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 35
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 37
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 37
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 6
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 6
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 7
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 7
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
SELECT
PCFV.id,
PCFV.value,
PCFV.slug,
COUNT(*) AS total_products
FROM product_custom_field_values PCFV
JOIN product_details PD
ON PD.product_custom_field_id = 8
AND CONCAT(',', PD.product_custom_field_value, ',') LIKE CONCAT('%,', PCFV.id, ',%')
JOIN products P
ON P.id = PD.product_id
AND P.status = 'Live'
WHERE
PCFV.value != ''
AND PCFV.product_custom_field_id = 8
GROUP BY PCFV.id
ORDER BY PCFV.orders, PCFV.value
Italy, Sicily
France, Provence
Spain, Rioja
New Zealand, Marlborough
Argentina, Mendoza
