Solved

# How to add values in tree nodes

Posted on 2012-09-12
Medium Priority
341 Views
I want to add LPV,RPV,LAmount,RAmount.

select sum(LPV) as Lv, sum(RPV) as Rv, sum(LAmount + RAmount) as Total from ( select leftn as SId, LPV, RPV, LAmount, RAmount from amount inner join (select @sid:=(select group_concat(leftn,',',rightn) from amount where find_in_set(sid, @nsid) >= 1), @nsid:=@sid as fset from amount, (select @nsid:='AMSJOXOLO',@sid='')v )gr on find_in_set(leftn,fset) where leftn <> '' union select rightn as SId, LPV, RPV, LAmount, RAmount from amount inner join (select @sid:=(select group_concat(leftn,',',rightn) from amount where find_in_set(sid, @nsid) >= 1), @nsid:=@sid as fset from amount, (select @nsid:='AMSJOXOLO',@sid='')v )gr on find_in_set(rightn,fset) where rightn <> '')x

When i am execute this above code, I got output like below:

But this addition program shows wrong output.

The original output is:

Lv         Rv        Total
19.5      21.5    2050

I need the addition query to show the original values like above output.
amount.sql
0
Question by:jnj_web_solutions
1 Comment

LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 38391385
try
``````SELECT sum(LPV)AS Lv
, sum(RPV)AS Rv
, sum(LAmount + RAmount)AS Total
FROM (SELECT DISTINCT Sid -- leftn AS SId
, LPV
, RPV
, LAmount
, RAmount
FROM amount
INNER JOIN (SELECT @sid := (SELECT group_concat(leftn, ',', rightn) FROM amount WHERE find_in_set(sid, @nsid)>= 1)
, @nsid :=@sid AS fset
FROM amount
, (SELECT @nsid := 'AMSJOXOLO' ,@sid = '')v)gr
ON find_in_set(leftn, fset)
WHERE leftn <> ''
UNION
SELECT DISTINCT Sid -- rightn AS SId
, LPV
, RPV
, LAmount
, RAmount
FROM amount
INNER JOIN (SELECT @sid := (SELECT group_concat(leftn, ',', rightn) FROM amount WHERE find_in_set(sid, @nsid)>= 1)
, @nsid :=@sid AS fset
FROM amount
, (SELECT @nsid := 'AMSJOXOLO' ,@sid = '')v)gr
ON find_in_set(rightn, fset)
WHERE rightn <> '')x
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month13 days, 14 hours left to enroll

#### 755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.