How to write soql nested query( Quantity(LastYear), Quantity(LasttoLastYear ) for same object( Invoice line),

Question

How to write soql nested query( Quantity(LastYear), Quantity(LasttoLastYear ) for same object( Invoice line),

I want to display Product, Quantity(CurrentYear), Quantity(LastYear), Quantity(LasttoLastYear ) and Amount from Object(InvoiceLine)

SELECT Product__c,Quantity__c,[SELECT Quantity__c FROM Invoice_Lines__r WHERE Account__c ='00150000017JsFoAAK' AND Invoice_Date__c = LAST_N_YEARS:2],[SELECT Quantity__c FROM Invoice_Lines__r WHERE Account__c ='00150000017JsFoAAK' AND Invoice_Date__c = LAST_N_YEARS:1],[SELECT Quantity__c FROM Invoice_Lines__r WHERE Account__c ='00150000017JsFoAAK' AND Invoice_Date__c = LAST_YEAR],Total_Price__c FROM Invoice_Lines__c
WHERE Account__c ='00150000017JsFoAAK'

its giving error Unknown error parsing query

Answer 1

What you're looking for is likely best handled by an aggregate SOQL query. Here's an example (but no promise it will work since you seem to be using all custom objects/fields):

SELECT CALENDAR_YEAR(Invoice_Date__c), Product__c, SUM(Quantity__c)
FROM Invoice_Lines__c
WHERE Account__c = '00150000017JsFoAAK'
AND Invoice_Date__c = LAST_N_YEARS:2
GROUP BY CALENDAR_YEAR(Invoice_Date__c), Product__c