CODEWITHSUNDEEP
mysql
Daniel Li
Daniel Li

Reputation: 397

MySql: Complex Aggregate Query

I have the data shown as follows:

table: School_Work

ID  Pay_Date    Hours   Amount
1   1/5/2014    5       20
1   1/5/2014    7       30
1   1/5/2014    10      25
1   1/8/2014    15      30
1   1/9/2014    18      25
2   1/7/2014    5       15
2   1/7/2014    5       10
2   1/9/2014    7       15
2   1/9/2014    9       26
2   1/9/2014    10      100

I have the following query atm:

SELECT ID, min(Pay_Date) as Start_Date, max(Pay_Date) as End_Date, sum(Amount) As Total_Without_Beginning_Start_Date
FROM School_Work
GROUP BY ID

The code works fine but I need it to NOT include the Start_Date calculating the Total Amount.

ID 1 should only be

30+25

ID 2 should only be

15+26+100

I tried using the having clause also but that didnt work. Thanks your input~

I need the result to be

ID   Start_Date    End_Date   Total_Without_Beginning_Start_Date
1    1/5/2014      1/9/2014   55
2    1/7/2014      1/9/2014   141

Upvotes: 2

Views: 217

Answers (1)

Ruslan
Ruslan

Reputation: 2799

You can accomplish this with a sub-query (generally a bad idea, but its fine for lightweight or 1-time queries). Try this...

SELECT x.ID, 
       min(x.Pay_Date) as Start_Date, 
       max(x.Pay_Date) as End_Date, 
       sum(x.Amount) - (select sum(y.Amount) from School_Work y where y.ID = x.ID and y.Pay_Date = min(x.Pay_Date) order by y.Pay_Date asc limit 1) As Total_Without_Beginning_Start_Date
FROM School_Work x
GROUP BY x.ID

Upvotes: 2

Related Questions