SamTay SamTay - 3 months ago 8
SQL Question

How to count values conditionally in SQL

I have an index table of entity attribute values that looks like this:


+-----------+--------------+----------+-------+
| entity_id | attribute_id | store_id | value |
+-----------+--------------+----------+-------+
| 38 | 190 | 1 | 22 |
| 38 | 190 | 1 | 23 |
| 39 | 190 | 1 | 22 |
| 39 | 190 | 1 | 23 |
| 39 | 190 | 1 | 42 |
| 40 | 190 | 1 | 22 |
| 41 | 190 | 1 | 54 |
| 42 | 190 | 1 | 54 |
| 43 | 190 | 1 | 22 |
| 44 | 190 | 1 | 22 |
| 45 | 190 | 1 | 54 |
+-----------+--------------+----------+-------+


As you can see, a single entity can have multiple values for a single attribute (
entity_id
38 has
value
s 22,23) and these values are not unique per entity (
entity_id
38,39 both share
value
22).

The first problem to solve is getting the number of distinct entities per value; this is easily accomplished with:

SELECT value, COUNT(entity_id) AS count
FROM catalog_product_index_eav
WHERE attribute_id=190
GROUP BY value;


which results in:

+-------+-------+
| value | count |
+-------+-------+
| 22 | 5 |
| 23 | 2 |
| 42 | 1 |
| 54 | 3 |
+-------+-------+


My question is how can I nest an OR condition in this count, namely: for some specific value Y, for each value X, count the number of entities that have either value X or Y.

I would like to do this in a single query. For instance, for
attribute_id
190 and
value
23, the output from above example should be:

+-------+-------+
| value | count |
+-------+-------+
| 22 | 5 | # all entities with value 22 happen to have 23 as well
| 23 | 2 | that is, one is a subset of the other
| 42 | 2 | # intersection is nonempty
| 54 | 5 | # sets are disjoint
+-------+-------+

Answer
select c1.value, 
( SELECT COUNT(DISTINCT entity_id) as count 
  FROM catalog_product_index_eav 
  where attribute_id=81 
  and (value=c1.value || value=7) ) as count 
FROM catalog_product_index_eav c1 
WHERE attribute_id=81 
GROUP BY c1.value