[Solved] Excel Grouping (or using SQL) [closed]

Question

” So created a SQL report to generate data that looks like this:”

The reason for your downvotes is likely due to you not posting the SQL code here. Leaves me guessing at your table formats, so for my ease… select * from mytable is what I’ll guess you’ve used. I’m left guessing column names too…in future questions, more information is helpful so I assume less.

select t1.client, t2.client, t3.client, t4.client, t1.parent, t2.parent, t3.parent, t1.*
from maytable t1
left join mytable t2 on t1.parent = t2.client
left join mytable t3 on t2.parent = t3.client
left join mytable t4 on t3.parent = t4.client
etc pending how deep this relation goes.

This will give you a list showing the parents. The t1.* is simply to grab the data columns related, you should write out your columns in full, I’m being lazy since no column list was supplied. The client IDs should look like this in your example (I’ve ignored parentID in my list below)

row 1 - 1 , 0, null , null
row 2 - 2 , 1 , 0, null
row 3 - 3 , 2 , 1, 0

Now we need a case statement…when the parent = 0 then show that client id.

select client_id, case when t1.parentid = 0 then t1.client_id
                      when t2.parentid = 0 then t2.client_id
                      when t3.parentid = 0 then t3.client_id
                      when t4.parentid = 0 then t4.clientID
                  end as parent_ID
      , t1.*
from (same as above query)

You can take what was just written above and call it a sub query and select from it, this time grabbing the sums of the columns you want.

select parent_id, sum(a), sum(b), etc...
from   
  (select client_id, case when t1.parentid = 0 then t1.client_id
                      when t2.parentid = 0 then t2.client_id
                      when t3.parentid = 0 then t3.client_id
                      when t4.parentid = 0 then t4.clientID
                  end as parent_ID
      , t1.*
       from maytable t1
left join mytable t2 on t1.parent = t2.client
left join mytable t3 on t2.parent = t3.client
left join mytable t4 on t3.parent = t4.client
etc pending how deep this relation goes. ) a
group by parent_id

Unfortunately this is the best code I can provide with the limited info you’ve given.

Accepted Answer