SQL aggregates 1

[PaulAH]

I would be grateful if someone could help me out with this. I have a sql statement (see below) which is dynamically built from vb. The sql works perfectly exept one line marked with ***



SELECT LBTRMATR.PROJECT_NO, max(LBBALCDE.Name) As BalName, '' As LedgerId , max(LBTRMATR.USER_ID) as 'Author',max(LBTRMATR.NAME) as 'Project Desc', max(LBPRJARC.ARCH_NO)as 'Archive',

min(case when LBBALCDE.Name = 'Project(Updated)' and LBFMBANK.Name = 'ASB TRUST A/C' then LBPRJBAL.Balance end) as 'Project(Updated)/ASB TRUST A/C',

min(case when LBBALCDE.Name = 'Project(Updated)' and LBFMBANK.Name = 'ASB TRUST A/C' and LBBALCDE.balance_code_Id in ('P', 'Z') and lbtrtran.tran_status >= 2 then lbtrtran.tran_status end) as 'Project(Updated)/ASB TRUST A/C Pending' ,

min(case when LBBALCDE.Name = 'Work In Progress' and LBFMBANK.Name = 'ASB TRUST A/C' then LBPRJBAL.Balance end) as 'Work In Progress/ASB TRUST A/C',

min(case when LBBALCDE.Name = 'Work In Progress' and LBFMBANK.Name = 'ASB TRUST A/C' and LBBALCDE.balance_code_Id in ('P', 'Z') and lbtrtran.tran_status >= 2 then lbtrtran.tran_status end) as 'Work In Progress/ASB TRUST A/C Pending' ,

**************************************************************************************************************

min(case when lbprjbal.Balance_Code_id ='P' and lbprjbal.client_acc_no = ' 00023' and lbfmbank.ledgtype_id=1 then sum(LBPRJBAL.Balance) end) as 'Project(Updated)/All Interest Bearing',

*********************************************************************************************

max(case when lbtrtran.client_acc_no = '00023' and lbfmbank.ledgtype_id=1 then lbtrtran.tran_status end) as 'Project(Updated)/All Interest Bearing Pending' ,

FROM LBTRTRAN RIGHT OUTER JOIN LBPRJBAL INNER JOIN LBTRMATR ON LBPRJBAL.PROJECT_NO = LBTRMATR.PROJECT_NO AND LBPRJBAL.CLIENT_ACC_NO = LBTRMATR.CLIENT_ACC_NO INNER JOIN LBFMBANK ON LBPRJBAL.LEDGER_ID = LBFMBANK.LEDGER_ID INNER JOIN LBBALCDE ON LBPRJBAL.BALANCE_CODE_ID = LBBALCDE.BALANCE_CODE_ID ON LBTRTRAN.CLIENT_ACC_NO = LBTRMATR.CLIENT_ACC_NO AND LBTRTRAN.PROJECT_NO = LBTRMATR.PROJECT_NO

AND LBTRTRAN.LEDGER_ID = LBFMBANK.LEDGER_ID LEFT OUTER JOIN LBPRJARC ON LBTRMATR.PROJECT_NO = LBPRJARC.PROJECT_NO AND LBTRMATR.CLIENT_ACC_NO = LBPRJARC.CLIENT_ACC_NO Where LBTRMATR.ACTIVE= 1 AND LBTRMATR.CLIENT_ACC_NO='00023' And LBTRMATR.PROJECT_NO<10000 Group by LBTRMATR.PROJECT_NO ORDER BY LBTRMATR.PROJECT_NO DESC

I understand I can not use Aggregae inside aggregate but I have no idea how to get round it.
I would be grateful for any help.
Thanks

 

[gmmastros]

You're right. You cannot use an aggregate within an aggregate.

However, you may be able to accomplish this using a derived table. Your query is pretty complicated, so let me show you an example.

[tt][blue]
Select ColumnX,
ColumnY,
Min(AliasName.AliasForSumColumn)
From TableX
Inner Join (
Select UniqueIdColumn,
Sum(ColumnName) As AliasForSumColumn
From SomeTable
Group By UniqueIdColumn
) As AliasName
On TableX.ColumnX = AliasName.UniqueIdColumn
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PaulAH]

Thanks George.
I have tried that but unsuccesfully. Here's what I tried to do. When I run this it says Invalid object name blahtable.
I tried without (), etc... and can't get the syntex right.
Any ideas?
Please look in the code inclosed with *'s

SELECT LBTRMATR.PROJECT_NO, max(LBBALCDE.Name) As BalName, '' As LedgerId , max(LBTRMATR.USER_ID) as 'Author',max(LBTRMATR.NAME) as 'Project Desc', max(LBPRJARC.ARCH_NO)as 'Archive',

min(case when LBBALCDE.Name = 'Project' and LBFMBANK.Name = 'ASB SAVINGS' then LBPRJBAL.Balance end) as 'Project/ASB SAVINGS',

min(case when LBBALCDE.Name = 'Project' and LBFMBANK.Name = 'ASB SAVINGS' and LBBALCDE.balance_code_Id in ('P', 'Z') and lbtrtran.tran_status >= 2 then lbtrtran.tran_status end) as 'Project/ASB SAVINGS Pending' ,

--**********************************************************

(Select blah from (Select lbprjbal.Project_no, sum(lbprjbal.Balance) as 'blah' from lbprjbal, lbfmbank where lbfmbank.ledger_id = lbprjbal.ledger_id and lbprjbal.Balance_Code_id ='P' and lbprjbal.client_acc_no = '00023' and lbfmbank.ledgtype_id=1 group by lbprjbal.Project_No) blahtable),



--*************************************************************

max(case when lbtrtran.client_acc_no = '00023' and lbfmbank.ledgtype_id=1 then lbtrtran.tran_status end) as 'Project(Updated)/All Interest Bearing Pending'

FROM LBTRTRAN RIGHT OUTER JOIN LBPRJBAL INNER JOIN LBTRMATR ON LBPRJBAL.PROJECT_NO = LBTRMATR.PROJECT_NO AND LBPRJBAL.CLIENT_ACC_NO = LBTRMATR.CLIENT_ACC_NO

INNER JOIN LBFMBANK ON LBPRJBAL.LEDGER_ID = LBFMBANK.LEDGER_ID

--*********************************************************************

inner join blahtable on blahtable.project_no = lbprjbal.project_no



--************************************************

INNER JOIN LBBALCDE ON LBPRJBAL.BALANCE_CODE_ID = LBBALCDE.BALANCE_CODE_ID ON LBTRTRAN.CLIENT_ACC_NO = LBTRMATR.CLIENT_ACC_NO AND LBTRTRAN.PROJECT_NO = LBTRMATR.PROJECT_NO

AND LBTRTRAN.LEDGER_ID = LBFMBANK.LEDGER_ID LEFT OUTER JOIN LBPRJARC ON LBTRMATR.PROJECT_NO = LBPRJARC.PROJECT_NO AND LBTRMATR.CLIENT_ACC_NO = LBPRJARC.CLIENT_ACC_NO

Where LBTRMATR.ACTIVE= 1 AND LBTRMATR.CLIENT_ACC_NO='00023' And LBTRMATR.PROJECT_NO<10000 Group by LBTRMATR.PROJECT_NO ORDER BY LBTRMATR.PROJECT_NO DESC

 

[gmmastros]

Ok. I took a look at it and got a little .

Now, understand that I have no clue what any of this represents, or if the changes I made will actually work. The 'complication factor' of this query makes it difficult for someone without intimate knowledge of your schema to actually do anything. So, please understand if the suggested changes do not work.

Also, please understand that I need to re-format the code so that I could make sense of it.

SELECT LBTRMATR.PROJECT_NO, 
       max(LBBALCDE.Name) As BalName, 
       '' As LedgerId , 
       max(LBTRMATR.USER_ID) as 'Author',
       max(LBTRMATR.NAME) as 'Project Desc', 
       max(LBPRJARC.ARCH_NO)as 'Archive', 
       min(case when LBBALCDE.Name = 'Project' and LBFMBANK.Name = 'ASB SAVINGS' then LBPRJBAL.Balance end) as 'Project/ASB SAVINGS', 
       min(case when LBBALCDE.Name = 'Project' and LBFMBANK.Name = 'ASB SAVINGS' and LBBALCDE.balance_code_Id in ('P', 'Z') and lbtrtran.tran_status >= 2 then lbtrtran.tran_status end) as 'Project/ASB SAVINGS Pending' ,
       [green]--***********************[/green]
       Min(BlahTable.Blah) As MaxBlah,
       [green]--***********************[/green]
       max(case when lbtrtran.client_acc_no = '00023' and lbfmbank.ledgtype_id=1 then lbtrtran.tran_status end) as 'Project(Updated)/All Interest Bearing Pending' 
FROM   LBTRTRAN 
       RIGHT OUTER JOIN LBPRJBAL	
       INNER JOIN LBTRMATR 
         ON LBPRJBAL.PROJECT_NO = LBTRMATR.PROJECT_NO 
         AND LBPRJBAL.CLIENT_ACC_NO = LBTRMATR.CLIENT_ACC_NO 
       INNER JOIN LBFMBANK  
         ON LBPRJBAL.LEDGER_ID = LBFMBANK.LEDGER_ID 
       [green]--*******************************************[/green]
       inner join (
          Select lbprjbal.Project_no, 
                 sum(lbprjbal.Balance) as 'blah' 
          from   lbprjbal, lbfmbank 
          where  lbfmbank.ledger_id = lbprjbal.ledger_id 
                 and lbprjbal.Balance_Code_id ='P' 
                 and lbprjbal.client_acc_no = '00023' 
                 and lbfmbank.ledgtype_id=1 
          group by lbprjbal.Project_No) As BlahTable
          on blahtable.project_no = lbprjbal.project_no
       [green]--**************************************[/green]
       INNER JOIN LBBALCDE 
         ON LBPRJBAL.BALANCE_CODE_ID = LBBALCDE.BALANCE_CODE_ID 
         ON LBTRTRAN.CLIENT_ACC_NO = LBTRMATR.CLIENT_ACC_NO 
         AND LBTRTRAN.PROJECT_NO = LBTRMATR.PROJECT_NO 
         AND LBTRTRAN.LEDGER_ID = LBFMBANK.LEDGER_ID 
       LEFT OUTER JOIN LBPRJARC 
         ON LBTRMATR.PROJECT_NO = LBPRJARC.PROJECT_NO 
         AND LBTRMATR.CLIENT_ACC_NO = LBPRJARC.CLIENT_ACC_NO 
Where  LBTRMATR.ACTIVE= 1 
       AND LBTRMATR.CLIENT_ACC_NO='00023' 
       And LBTRMATR.PROJECT_NO<10000 
Group by LBTRMATR.PROJECT_NO 
ORDER BY LBTRMATR.PROJECT_NO DESC

Notice how I moved the query to the FROM section. When you do this, it's called a derived table. This is a very powerful concept that you should study and get comfortable with.

Also, realize that you may need to change the INNER JOIN (for the derived table) to a Left Join. You may need to play around with that to make sure you are getting the correct results.

Hopefully this helps. Please let me know either way.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PaulAH]

George,
I copied the code, paste it in Query Analyser and ran it.
it worked absolutely perfectly. My figures are correct and I did not have to change any syntex.
I can't thank you enough. I had about a dozen people looking at this and no one gave me the correct answer.
Thanks lots.

 

[gmmastros]

Paul,

Thanks for the feedback. I'm glad I could help.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom