CODEWITHSUNDEEP
mysqlsql
Fredy
Fredy

Reputation: 2910

How to get the number of buyers on each main channel based on the number of buyers on each sub-channel

I need help to make the mysql query to get the number of buyers on each main channel based on the number of buyers on each sub-channel

Basically I have two tables, namely tbl_channel and tbl_buyer as below:

enter image description here

Conditions:

  1. I just show number of buyers every Master Channel (parentid = 0) based on the number of buyers on each sub-channel
  2. Every buyer in a channel_id (TBL_BUYER) with same buyer_id will count as one buyer

The results I want:

enter image description here

So far I just can only make a query to display the number of buyers at a sub-channel only, like this below:

SELECT count(aa.buyer_id) as Number_of_buyers FROM (SELECT b.buyer_id
FROM tbl_channel a 
RIGHT JOIN tbl_buyer b on (a.id=b.channel_id)
WHERE a.parentid_channel_id = 2 /* PARENT ID */
GROUP by b.buyer_id ORDER BY b.channel_id) aa

Can you help me make a complete query to produce results as shown above? Thank you before :)

Upvotes: 1

Views: 64

Answers (1)

John Woo
John Woo

Reputation: 263843

SELECT  a.channel_id `Channel ID`,
        a.channel_title `Channel Title`,
        COUNT(DISTINCT c.buyer_id) `Number of Buyers`
FROM    tbl_Channel a
        INNER JOIN tbl_Channel b
            ON a.channel_ID = b.parent_channel_id
        INNER JOIN tbl_buyer c
            ON  b.channel_ID =  c.channel_ID
WHERE   a.parent_channel_id = 0
GROUP BY a.channel_id,
         a.channel_title

Upvotes: 3

Related Questions