CODEWITHSUNDEEP
mysqlcount
Andrea
Andrea

Reputation: 45

Mysql sum count occurrences in multiple columns from another table

I need to find the sum of occurrences of ids in a join table. The id could be present in two different columns (id_type_1 and id_type_2).

Table types

id | name
1  | Test1
2  | Test2
3  | Test3

Table products

id | name     | id_type_1 | id_type_2
1  | Product1 | 1         | 2
2  | Product2 | 3         | 1
3  | Product3 | 1         | 3

I need to get a result like this:

Type  | Total
Test1 | 3
Test2 | 1
Test3 | 2

Here's my query, but it takes several seconds to execute:

SELECT t.name, 
(SELECT COUNT(p.id) FROM products p WHERE p.id_type_1 = t.id || p.id_type_2 = t.id) AS total 
FROM types t 
WHERE 1 
ORDER BY total DESC

Is there a more effective way to achieve the result?

Upvotes: 1

Views: 823

Answers (1)

forpas
forpas

Reputation: 164174

Join the tables and aggregate:

select t.id, t.name,
       sum((t.id = p.id_type_1) + (t.id = p.id_type_2)) Total
from types t inner join products p
on t.id in (p.id_type_1, p.id_type_2)
group by t.id, t.name

If there is no case for the id to exist in both id_type_1 and id_type_2 in the same row then:

select t.id, t.name,
       count(*) Total
from types t inner join products p
on t.id in (p.id_type_1, p.id_type_2)
group by t.id, t.name

See the demo.
Results:

> id | name  | Total
> -: | :---- | ----:
>  1 | Test1 |     3
>  2 | Test2 |     1
>  3 | Test3 |     2

Upvotes: 1

Related Questions