-2

I have a table in which there are four column ID,MID,SID and Amt, I prepare the query for count the sum of amt using level.

Like: 

SELECT Sum(amt) as level1 FROM adm where sid='MT00001';

SELECT Sum(amt) as level2 FROM adm where sid in (SELECT mid FROM adm where sid='MT00001');

SELECT Sum(amt) as level3 FROM adm where sid in (SELECT mid FROM adm where sid in (SELECT mid FROM adm where sid='MT00001'));

SELECT Sum(amt) as level4 FROM adm where sid in (SELECT mid FROM adm where sid in (SELECT mid FROM adm where sid in (SELECT mid FROM adm where sid='MT00001')))

but took so much time and i want this till level 15th

Salman Zafar
  • 3,844
  • 5
  • 20
  • 43
Neha Jain
  • 1
  • 1
  • 2
    Just explain the logic why you came up nested sub queries or share the table and sample data – James Jul 17 '19 at 09:08
  • As I understand you need to calculate the amount for each level in a management hierarchy. There are 15 levels and you need to find the total sum for each level. You can use recursive SQL queries. Please see: https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Nadir Latif Jul 17 '19 at 09:27
  • no its not working – Neha Jain Jul 17 '19 at 12:14

1 Answers1

0
EXPLAIN [your query here]

This will help you understand the current status of your query and tables.

Adding Index on your table will help additional performance on your queries.

dodzb
  • 379
  • 2
  • 5
  • Hi actually i am preparing the mlm software in which i need to calculate sum of amt from that particular ID till 15th level now i am using 15 query for sum the amt and it will work fine but it tooks so much time i sent you the query what i am doing – Neha Jain Jul 17 '19 at 09:25