Can't get thousands seperator to work in SQL error 2

[vba317]

I have tried many different combinations I am posting my current combination.
My current error is Incorrect syntax near the keyword as
Any help is appreciated
Tom

SELECT ChgBillMonth
,(CONVERT (VARCHAR(100),CAST((ChargeAmt as Money),1) 
----,sum(CurrentBalance) as Balance
--,sum(ChargeAmt) as ChargeAmt
--,sum(CurrentBalance) as Balance
FROM dbo.rpt_Infmtx_CurrentAR
WHERE ChgBillMonth='8/1/2013'
GROUP BY ChgBillMonth

 

[gk53]

it should be

SELECT ChgBillMonth
,(CONVERT (VARCHAR(100),CAST(ChargeAmt as Money),1) 
----,sum(CurrentBalance) as Balance
--,sum(ChargeAmt) as ChargeAmt
--,sum(CurrentBalance) as Balance
FROM dbo.rpt_Infmtx_CurrentAR
WHERE ChgBillMonth='8/1/2013'
GROUP BY ChgBillMonth

you have extra ( after cast

 

[SQLBill]

Also, remove the ( before Convert as it isn't necessary.

,(CONVERT (VARCHAR(100),CAST(ChargeAmt as Money),1)
1 2 3 3 4 4 2

_SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[vba317]

gk53 your suggestion did work, but I also need the chargeAmt to be summed. When I add sum I get the error Sum is not a recognized function

 

[PDreyer]

CONVERT (VARCHAR(100),CAST(sum(ChargeAmt) as Money),1)

 

[gk53]

the query like below will work, but i do not think it will give you result you expected

SELECT ChgBillMonth
,CONVERT (VARCHAR(100),CAST(ChargeAmt as Money),1) as  ChargeAmt 
,sum(CurrentBalance) as Balance
,sum(ChargeAmt) as ChargeAmtSum
,sum(CurrentBalance) as Balance
FROM dbo.rpt_Infmtx_CurrentAR
WHERE ChgBillMonth='8/1/2013'
GROUP BY ChgBillMonth, ChargeAmt

the problem is ChargeAmt and ChargeAmtSum. ChargeAmtSum will be total of the same values of ChargeAmt in table for current month Just look on my example

create table #T
(
ChgBillMonth date, 
ChargeAmt money,
CurrentBalance money
)

insert into #T
select '8/1/2013', 100, 9000
union 
select '8/1/2013', 100, 8900
union 
select '8/1/2013', 200, 8700
union 
select '8/1/2013', 150, 8550

SELECT ChgBillMonth
,CONVERT (VARCHAR(100),CAST(ChargeAmt as Money),1) as  ChargeAmt 
,sum(CurrentBalance) as Balance
,sum(ChargeAmt) as ChargeAmtSum
--,sum(CurrentBalance) as Balance
FROM #T
WHERE ChgBillMonth='8/1/2013'
GROUP BY ChargeAmt, ChgBillMonth

I created table with your fields , insert 4 row with data and run your query and resuls looks like
ChgBillMonth ChargeAmt Balance ChargeAmtSum
2013-08-01 100.00 17900.00 200.00
2013-08-01 150.00 8550.00 150.00
2013-08-01 200.00 8700.00 200.00

If you have ChargeAmt in select you must use group by ChargeAmt

 

[chamilz]

but I also need the chargeAmt to be summed

SELECT ChgBillMonth
,CONVERT (VARCHAR(100),SUM(CAST(ChargeAmt as Money)),1) as  ChargeAmt 
,sum(CurrentBalance) as Balance
,sum(ChargeAmt) as ChargeAmtSum
--,sum(CurrentBalance) as Balance
FROM #T
WHERE ChgBillMonth='8/1/2013'
GROUP BY  ChgBillMonth

 

[vba317]

chamilz, I just tried your solution and it worked great! Thanks!