Solved

# How to add values in tree nodes

Posted on 2012-09-12
331 Views
Last Modified: 2012-09-12
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

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!