CODEWITHSUNDEEP
sqlms-accessvba
GhostDZ9
GhostDZ9

Reputation: 155

Sum Case SQL Syntax error

I have the following SQL query:

SELECT 
    CompanyCode, PaymentStatus, PaymentType, PaySource,
    SUM(CCur(PaymentAmount)), 
    SUM(CASE WHEN PaymentStatus='APPROVED' THEN 1 ELSE 0 END) AS Approved,  
    COUNT(*) 
FROM 
    Detail_Work 
GROUP BY 
    CompanyCode, PaymentType, PaymentStatus, PaySource 
ORDER BY 
    CompanyCode, PaymentType, PaymentStatus, PaySource

And I get the following error:

Syntax Error (Missing Operator) in query Expression 'SUM(CASE WHEN PaymentStatus='APPROVED' THEN 1 ELSE 0 END)'

Upvotes: 0

Views: 1322

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

MS Access doesn't support case. Use iif() instead:

SELECT CompanyCode, PaymentStatus, PaymentType, PaySource,    
       SUM(CCur(PaymentAmount)),
       SUM(IIF(PaymentStatus = 'APPROVED', 1, 0)) AS Approved,  COUNT(*) 
FROM Detail_Work 
GROUP BY CompanyCode,PaymentType, PaymentStatus, PaySource 
ORDER BY CompanyCode, PaymentType, PaymentStatus, PaySource;

However, I'm not sure why you have PaymentStatus in the GROUP BY. Perhaps you intend:

SELECT CompanyCode, PaymentType, PaySource,    
       SUM(CCur(PaymentAmount)),
       SUM(IIF(PaymentStatus = 'APPROVED', 1, 0)) AS Approved,  COUNT(*) 
FROM Detail_Work 
GROUP BY CompanyCode,PaymentType, PaySource 
ORDER BY CompanyCode, PaymentType, PaySource;

Upvotes: 1

Related Questions