Syntax error in multiple SELECT subqueries under AGGREGATE functions

I'm trying to use aggregate functions and inside them are subqueries. In my understanding of SQL construction. I came up with this. But it says syntax error. Can anybody help me reconstruct the statement? The original is so long with INNER JOIN clauses so I just removed them because the aggregate functions' part is just the part where it's going nuts. Thanks

SELECT CCompany,
       COUNT(SELECT * FROM Table1 WHERE (CPaymentAmount<CFinalTotal and CStatus='Shipped')) as UnpaidTransactionCount,
       COUNT(SELECT * FROM Table1 WHERE (CPaymentAmount=CFinalTotal and CStatus='Shipped')) as PaidTransactionCount,
       SUM(SELECT CFinalTotal FROM Table1 WHERE (CPaymentAmount<CFinalTotal and CStatus='Shipped')) as UnpaidTotal,
       SUM(SELECT CPaymentAmount FROM Table1 WHERE (CPaymentAmount<CFinalTotal and CStatus='Shipped')) as PartialPayment 
       FROM Table1 
       WHERE CStatus='Shipped'

It says syntax error near 'SELECT' and 'as'. Basically I'm trying to count transactions that are unpaid and paid, then sum up everything to get the balance. Balance is solved using my scripting language. Thanks.

Answers


I think you just need:

SELECT CCompany,
   SUM(CASE WHEN CPaymentAmount<CFinalTotal THEN 1 ELSE 0 END) as UnpaidTransactionCount,
   SUM(CASE WHEN CPaymentAmount=CFinalTotal THEN 1 ELSE 0 END) as PaidTransactionCount,
   SUM(CASE WHEN CPaymentAmount<CFinalTotal THEN CFinalTotal ELSE 0 END) as UnpaidTotal,
   SUM(CASE WHEN CPaymentAmount<CFinalTotal THEN CPaymentAmount ELSE 0 END) as PartialPayment 
FROM Table1 
WHERE CStatus='Shipped'
GROUP BY CCompany

Unless there's some reason why you think the subqueries are needed. They all seem to be about the same table, and whilst your subqueries weren't correlated on CCompany, I've taken the liberty of expecting that you were wanting a per-company result.

If this isn't what you were aiming for, some sample data and expected results would be much appreciated.


The inner statement will give you all columns as you mentioned select * statement. You can't use count with the result of inner query.

Check the statement.


Need Your Help

Outlook Add-In shows as active but isn't available in the ribbon

c# outlook vsto outlook-addin

I've created a simple Outlook add-in and I can't seem to get an install package together correctly.