Mysql count for distinct type

Question

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:

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:

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.

Gurwinder Singh · Accepted Answer · 2017-08-04 12:00:27Z

2

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;

edited Aug 4, 2017 at 12:00

answered Aug 4, 2017 at 11:51

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Luca Sepe Over a year ago

Thanks! This works perfectly. But i must admit my ignorance; we have the case option in sql? Or is PLSQL? and what is the effect of the then 1 end instruction?

Gurwinder Singh Over a year ago

The CASE is a sql keyword. The 1 is just a non null value, you can write any non null value there. Here, the case returns 1 when condition is satisfied and null when not. The count function only counts non nulls

C3roe Over a year ago

FYI, MySQL automatically treats true as 1 and false as 0, when those values are interpreted in a numeric context, so you should be able to shorten that to just count(e.type = 'Applications') as Applications (If you prefer the brevity; using a longer form that makes it more obvious on first glance what is going on, isn’t “wrong” of course.)

Gurwinder Singh Over a year ago

@CBroe - Yes agreed. but we need to use SUM instead of COUNT in that case, Added the edit

Luca Sepe Over a year ago

Thanks both of you for answer!

|

Collectives™ on Stack Overflow

Mysql count for distinct type

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related