?
Solved

How to add values in tree nodes

Posted on 2012-09-12
1
Medium Priority
?
341 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:
Output image

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
Comment
Question by:jnj_web_solutions
1 Comment
 
LVL 25

Accepted Solution

by:
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

Open in new window

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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.

Join & Ask a Question