I am creating referral system. Now I have two table one users and second is product. In User Table I Have two column one is user Id one is referred_by. Here is my table.
+----+-------------+
| id | referred_by |
+----+-------------+
| 35 | 0 |
| 36 | 35 |
| 40 | 35 |
| 41 | 35 |
| 44 | 41 |
| 45 | 36 |
| 46 | 41 |
+----+-------------+
Here is query. This query count number of referrals
select referred_by, count(*) from users group by referred_by.
But can I modify result like User ID 35 Have 3 Referral 40,41,and 46.
Now I have product table And user Id is in Product table.
+----+---------+---------------+
| id | user_id | product_price |
+----+---------+---------------+
| 1 | 37 | 300 |
| 2 | 38 | 300 |
| 3 | 40 | 300 |
| 4 | 41 | 300 |
| 5 | 42 | 300 |
| 6 | 44 | 300 |
| 7 | 45 | 300 |
| 8 | 35 | 300 |
| 9 | 35 | 300 |
| 10 | 35 | 300 |
| 11 | 35 | 300 |
| 12 | 46 | 300 |
+----+---------+---------------+
My INner Join query Here.
select users.id,users.referred_by
,orders_products.user_id,orders_products.product_price
from users
inner join orders_products on users.id=orders_products.user_id;
And output of this.
| id | referred_by | user_id | product_price |
+----+-------------+---------+---------------+
| 40 | 35 | 40 | 300 |
| 41 | 35 | 41 | 300 |
| 44 | 41 | 44 | 300 |
| 45 | 36 | 45 | 300 |
| 35 | 0 | 35 | 300 |
| 35 | 0 | 35 | 300 |
| 35 | 0 | 35 | 300 |
| 35 | 0 | 35 | 300 |
| 46 | 41 | 46 | 300 |
+----+-------------+---------+---------------+
Now. What I am trying to do. if user Id 35 Have 3 referrals 36,40 and 41 then calculate the sum of price of 35 ,36,40 and 41. Means . Please she my next level try @I am using this query .
select users.id,users.referred_by
,orders_products.user_id,sum(orders_products.product_price)
from users
inner join orders_products on users.id=orders_products.user_id group by user_id;
+----+-------------+---------+------------------------------------+
| id | referred_by | user_id | sum(orders_products.product_price) |
+----+-------------+---------+------------------------------------+
| 35 | 0 | 35 | 1200 |
| 40 | 35 | 40 | 300 |
| 41 | 35 | 41 | 300 |
| 44 | 41 | 44 | 300 |
| 45 | 36 | 45 | 300 |
| 46 | 41 | 46 | 300 |
+----+-------------+---------+------------------------------------+
Now How can I get Desire Out Put From This result. I am Trying to get Out put like this.
User_id ,referrals, total price
35, 3(36,40,42) , 20000(this will be total price of user 35,36,40,41)
