CODEWITHSUNDEEP
mysqlselectjoingroup-by
Laoneo
Laoneo

Reputation: 1566

Mysql get the amount of products

I have the following two tables

# vendor table
vendor_id     host
   1       192.168.0.2
   1       192.168.0.4
   1       192.168.0.6
   2       192.168.1.21
   2       192.168.1.23
   2       192.168.1.25
   2       192.168.1.27

# information table
    host        name
 192.168.0.2     bar
 192.168.0.4     bar1

What I need at the end is the following result set

vendor_id amount_live amount_total
    1         2            3
    2         0            4

The column amount_live is the amount of entries per vendor in the information table and the column amount_total is the amount of hosts in the vendor table per vendor.

Can some of the experts please tell me the mysql select statement to get the desired result set. Thanks.

Upvotes: 0

Views: 48

Answers (1)

sgeddes
sgeddes

Reputation: 62861

You can do this with count and an outer join:

select v.vendor_id, 
   count(i.host) amount_live,
   count(*) amount_total
from vendor v
    left join information i on v.host = i.host
group by v.vendor_id

Upvotes: 1

Related Questions