CODEWITHSUNDEEP
mysql
Luca Sepe
Luca Sepe

Reputation: 805

Mysql count for distinct type

I have 2 table in a MySQL DB, hosts and events, that can join thanks and ID field. A particular field of interest for my query is, for hosts table, name; for the events, type. If I make a join between them, an example result is:

enter image description here

So, in this image, you can see for example that the Host Achille ha 4 events: 2 of OS type, 1 of Application time and 1 of Service Type.

My question is: using aggregator operators, is it possible to make a tabel where, for every host, i can show how many events there are divided by type? More speficically, the desired table may have this header:

enter image description here

that, in aour previuos example, may return:

| Achille | 1 | 2 | 1 |
| Aiace   | 1 | 1 | 0 |
| Ulisse  | 0 | 0 | 1 |

My first try is this query:

    SELECT hosts.name, count(e1.type) as Applications, count(e2.type) as OS, count(e3.type) as Type 
    FROM hosts JOIN events e1 ON hosts.id = e1.host_id 
    JOIN events e2 ON hosts.id = e2.host_id 
    JOIN events e3 ON hosts.id = e3.host_id 
    WHERE e1.type = 'Applications' AND e2.type = 'OS' AND e3.type = 'Services' 
GROUP BY hosts.name;

but does not work.

Upvotes: 1

Views: 451

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You don't need to join the events table multiple times. Just do conditional aggregation.

SELECT h.name, 
    count(case when e.type = 'Applications' then 1 end) as Applications,
    count(case when e.type = 'OS' then 1 end) as OS,
    count(case when e.type = 'Services' then 1 end) as Services
FROM hosts h
JOIN events e ON h.id = e.host_id 
GROUP BY h.name;

or concisely, using sum

SELECT h.name, 
    sum(e.type = 'Applications') as Applications,
    sum(e.type = 'OS') as OS,
    sum(e.type = 'Services') as Services
FROM hosts h
JOIN events e ON h.id = e.host_id 
GROUP BY h.name;

Upvotes: 2

Related Questions