Cannot perform an aggregate function on an express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cannot perform an aggregate function on an express

Author

Topic

kaps04
Starting Member

1 Post

Posted - 2008-07-21 : 06:58:15

select BranchCode,
count (case when ProposalRecDate = '07/09/2008' then 1 end )Submission,
sum (case when ProposalRecDate = '07/09/2008' then PreAmount end )SubAmount,
count (case when IssueDate = '07/09/2008' and StateCode in ('IF','RD') then 1 end )Issue,
sum (case when IssueDate = '07/09/2008' and StateCode in ('IF','RD') then PreAmount end )IssueAmount,
count (case when ContractNo in (select ContractNo from RejectProposal where EffTraDate = '07/09/2008')
and ApplicationNo not in (select ApplicationNo from ChqBounceApp) then 1 end )Reject
count (case when ContractNo in (select ContractNo from RejectProposal where EffTraDate = '07/09/2008')
and ApplicationNo not in (select ApplicationNo from ChqBounceApp) then 1 end )Reject,
sum (case when ContractNo in (select ContractNo from RejectProposal where EffTraDate = '07/09/2008')
and ApplicationNo not in (select ApplicationNo from ChqBounceApp) then PreAmount end )RejectAmount,
count (case when ApplicationNo in (select ApplicationNo from ChqBounceApp)
and ProposalRecDate = '07/09/2008' then 1 end )ChqBounce,
sum (case when ApplicationNo in (select ApplicationNo from ChqBounceApp)
and ProposalRecDate = '07/09/2008' then PreAmount end )ChqBounceAmount,
count (case when ProposalRecDate = '07/09/2008' and StateCode in ('PS','UW')
and ApplicationNo not in (select ApplicationNo from ChqBounceApp) then 1 end )Pending,
sum (case when ProposalRecDate = '07/09/2008' and StateCode in ('PS','UW')
and ApplicationNo not in (select ApplicationNo from ChqBounceApp) then PreAmount end )PendingAmount
from ProposalDetails
group by BranchCode
order by BranchCode

please suggest will get error.how to solve it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 07:11:25

[code]SELECT pd.BranchCode,
SUM(CASE WHEN pd.ProposalRecDate = '07/09/2008' THEN 1 ELSE 0 END) AS Submission,
SUM(CASE WHEN pd.ProposalRecDate = '07/09/2008' THEN pd.PreAmount ELSE 0 END) AS SubAmount,
SUM(CASE WHEN pd.IssueDate = '07/09/2008' AND pd.StateCode IN ('IF', 'RD') THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pd.IssueDate = '07/09/2008' AND pd.StateCode IN ('IF', 'RD') THEN pd.PreAmount ELSE 0 END) AS IssueAmount,
SUM(CASE WHEN rp.ContractNo IS NOT NULL AND cba.ApplicationNo IS NULL THEN 1 ELSE 0 END) AS Reject,
SUM(CASE WHEN rp.ContractNo IS NOT NULL AND cba.ApplicationNo IS NULL THEN pd.PreAmount ELSE 0 END) AS RejectAmount,
SUM(CASE WHEN cba.ApplicationNo IS NOT NULL AND pd.ProposalRecDate = '07/09/2008' THEN 1 ELSE 0 END) AS ChqBounce,
SUM(CASE WHEN cba.ApplicationNo IS NOT NULL AND pd.ProposalRecDate = '07/09/2008' THEN pd.PreAmount ELSE 0 END) AS ChqBounceAmount,
SUM(CASE WHEN pd.ProposalRecDate = '07/09/2008' AND pd.StateCode IN ('PS', 'UW') AND cba.ApplicationNo IS NULL THEN 1 ELSE 0 END) AS Pending,
SUM(CASE WHEN pd.ProposalRecDate = '07/09/2008' AND pd.StateCode IN ('PS', 'UW') AND cba.ApplicationNo IS NULL THEN pd.PreAmount ELSE 0 END) AS PendingAmount
FROM ProposalDetails AS pd
LEFT JOIN (
SELECT ContractNo
FROM RejectProposal
WHERE EffTraDate = '07/09/2008'
GROUP BY ContractNo
) AS rp ON rp.ContractNo = pd.ContractNo
LEFT JOIN (
SELECT ApplicationNo
FROM ChqBounceApp
GROUP BY ApplicationNo
) AS cba ON cba.ApplicationNo = pd.ApplicationNo
GROUP BY pd.BranchCode
ORDER BY pd.BranchCode[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources