2

I have the following table called Tracking: 

ID_path -- step_start -- step_end -- time  
  1         A             B      10  
  1         B             C      20  
  1         C             D      30  
  2         A             C      100  
  3         D             C      20  
  3         A             N      300

I'm looking for a single query in Mysql that can extract the cumulate value for path A-C, so itself and all step inside: [A-C] + [A-B] + [B-C].

Select sum(time) from tracking where (step_start = 'A' or step_end = 'C') and ID_path = ID_path

I thought this query considering that the value of step can't be both in start and end but I don't know how to express the last condition (ID_path = ID_path) in order to extract only the record that have the same id_path. Is there a specific SQL function?

Thanks for any help / mysql coding guidelines / advice

SRIRAM
  • 1,888
  • 2
  • 17
  • 17
Sandra
  • 67
  • 5
  • I think you should refer to http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries – Sashi Kant Nov 15 '12 at 07:35
  • @SashiKant What does this have to do with hierarchy? – Barmar Nov 15 '12 at 07:37
  • @Barmar: If you go through the question the requirement is nearly same – Sashi Kant Nov 15 '12 at 07:38
  • That question has nothing to do with summing anything, it's about turning a parent-child table into PHP nested arrays. – Barmar Nov 15 '12 at 07:41
  • 1
    @SashiKant It seems like your recommendation would be better in http://stackoverflow.com/questions/13392648/php-nested-array-from-sql-repeating-results – Barmar Nov 15 '12 at 07:42

2 Answers2

1

you could do this:

Select ID_path,sum(time)
from tracking 
where (step_start = 'A' or step_end = 'C')
group by ID_path


SQl fiddle demo

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
  • Thanks Joe but I need the final value in the example it's 130 (100 from A-C and 30 grouping A-B and B-c. – Sandra Nov 15 '12 at 07:47
0

I'm not exactly sure what you're asking, but I suspect this is it:

SELECT id_path, sum(time) total
WHERE step_start = 'A' OR step_end = 'C'
GROUP BY id_path
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks Barmar but I need the final value, in the example it's 130 (100 from A-C and 30 grouping A-B and B-c). – Sandra Nov 15 '12 at 08:52
  • Can there be longer paths, like A-B, B-D, D-E, E-F, F-C? – Barmar Nov 15 '12 at 08:54
  • Yes but in the same path the step can be included only 1 at start and at end, like train path. – Sandra Nov 15 '12 at 09:00
  • This is difficult to do in SQL, because it requires an iterative or recursive algorithm to find the complete path. Can you move it into the calling application? – Barmar Nov 15 '12 at 09:06
  • If there isn't other solution I must use some php script to iterate :-) – Sandra Nov 15 '12 at 12:44