CODEWITHSUNDEEP
phpmysql
sammry
sammry

Reputation: 412

Looking for Top Line User Cumulitive report for Downline Users in PHP MYSQL

User Table
------------------------------------------
|userid |type   |reseller   |username   |
------------------------------------------
|1      |A      |1          |admin      |
|2      |R      |1          |user2      |
|3      |R      |1          |user3      |
|4      |C      |2          |user4      |
|5      |C      |2          |user5      |
|6      |C      |3          |user6      |
|7      |C      |3          |user7      |
------------------------------------------

A for admin, R for reseller and C for client

SMS Summary table
----------------------------------
|userid |totalsms   |date       |
----------------------------------
|2      |10         |13-01-2015 |
|3      |20         |13-01-2015 |
|4      |30         |13-01-2015 |
|5      |30         |13-01-2015 |
|6      |40         |13-01-2015 |
|7      |50         |13-01-2015 |
|2      |20         |13-01-2015 |
----------------------------------

Result required

----------------------------------
|resellername   |totalsms       |
----------------------------------
|user2          |90             |
|user3          |110            |
----------------------------------

result is calculated as

 userid2 = `10+20` + downline userid4 and userid5 = `30+30`
 userid3 = `20` + downline userid6 and userid7 = `40+50`

Currently to achieve this, am running multiple queries like, first am querying all the userid from sms summary table and getting the username of the specific reseller from user table and then summing all the userid of 2.

I want to run this query once not multiple times, I tried several logics, but failed to achieve as I require it.

Please help... Thanks in advance...

Edited: I have tried till here, where I get individual report, but not for reseller report with join

SELECT SUM(s.totalsms) AS totalsms, ru.username as reseller FROM smssummary s 
    left join  users u on s.userid=u.userid
    left join  users ru on ru.userid = (SELECT `reseller` FROM `users` WHERE `userid` = u.userid)

Upvotes: 2

Views: 127

Answers (1)

radar
radar

Reputation: 13425

you can do aggregation for reseller and then aggregation for clients and then sum up the totals using left join to handle the case where reseller doesn't have any clients

SQL Fiddle Demo 

select T.username as reseller, IFNULL(sum(S.totalsms)+smsCount,0) as TotalSMS
FROM
(
   SELECT U.userid, U.username, IFNULL(sum(SR.totalsms),0) as smsCount
   from Users U
   join SMSSummary SR
   on U.type ='R'
   and SR.userid = U.userid
   group by U.userid, U.username

) T
left join Users U
on U.type ='C' 
and U.reseller = T.userid
left join SMSSummary S
on S.userid = U.userid
group by T.username

Upvotes: 1

Related Questions