Sql query sum all common column data

As I implied in my comment, I don't have the time to reconstruct your tables and your data from an image. So I am going to use this test data in my example here:

declare @demo table (PaymentCode int, paymentamount money, storeid int, otherdata money)
insert into @demo(PaymentCode, paymentamount, storeid) values
(1, 100.10, 1,10.1),	-- CWM, Cash
(2, 200.20, 1,20.2),	-- CWM, Cash
(1, 300.30, 2,30.3),	-- CWD, Cash
(2, 400.40, 2,40.4)	-- CWD, Other

I always let my SQL statements "evolve" from just getting the data I need back out of the table. This includes any manipulation I need to do to specific columns and omits any columns I'm not really interested in ...

select 
	CASE 
		WHEN StoreID = 1 THEN 'CWM'
		WHEN StoreID = 2 THEN 'CWD' 
		ELSE 'Unknown'
	END as accountcode,
	CASE WHEN PaymentCode = 1 THEN 'Cash' ELSE 'Other' END AS PaymentMethod,
	case when PaymentCode <> 1 then paymentamount end as OtherPaymentAmount,
	case when PaymentCode = 1 then paymentamount end as CashAmount
from @demo

So from that I get these results

accountcode	PaymentMethod	OtherPaymentAmount	CashAmount
CWM			Cash			NULL				100.10
CWM			Other			200.20				NULL
CWD			Cash			NULL				300.30
CWD			Other			400.40				NULL

Yuk. I'm going to deal with those NULL values now by adding an ELSE to my CASE statements:

select 
	CASE 
		WHEN StoreID = 1 THEN 'CWM'
		WHEN StoreID = 2 THEN 'CWD' 
		ELSE 'Unknown'
	END as accountcode,
	CASE WHEN PaymentCode = 1 THEN 'Cash' ELSE 'Other' END AS PaymentMethod,
	case when PaymentCode <> 1 then paymentamount else 0 end as OtherPaymentAmount,
	case when PaymentCode = 1 then paymentamount else 0 end as CashAmount
from @demo

Giving

accountcode	PaymentMethod	OtherPaymentAmount	CashAmount
CWM			Cash			0.00				100.10
CWM			Other			200.20				0.00
CWD			Cash			0.00				300.30
CWD			Other			400.40				0.00

Better. Now to consider how I am going to group the information. I'm pretty sure that you started out with something like this

select 
	CASE 
		WHEN StoreID = 1 THEN 'CWM'
		WHEN StoreID = 2 THEN 'CWD' 
		ELSE 'Unknown'
	END as accountcode,
	CASE WHEN PaymentCode = 1 THEN 'Cash' ELSE 'Other' END AS PaymentMethod,
	sum(case when PaymentCode <> 1 then paymentamount else 0 end) as OtherPaymentAmount,
	sum(case when PaymentCode = 1 then paymentamount else 0 end) as CashAmount
from @demo
group by PaymentCode

and got an error along the lines of

Msg 8120, Level 16, State 1, Line 29
Column '@demo.storeid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 30
Column '@demo.storeid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So you probably just added that to the group by

group by PaymentCode,StoreID

In your case it looks like you carried on just adding the columns as they were reported as errors

group by PaymentCode, paymentamount, docentry, storeid,sapcreditcard, FileName, BPA_ProcessStatus,ERP_PaymentProcessed

But when you run that, the results are the same as if you hadn't used GROUP BY at all!

Think about what the GROUP BY is trying to do ... group things together into sets. The more columns you add to a GROUP BY clause, the more specific you make the set - until it becomes so specific that each set or group only contains a single row.

Let's take a step back and look at the requirements again ...

Quote:

What im looking for is, to sum all the items with same payment code

Also remember what I said earlier when I started constructing my query

Quote:

and omits any columns I'm not really interested in

In this case we are only interested in the paymentCode and the paymentamount, so get rid of everything else. The query then becomes very simple

select 
	CASE WHEN PaymentCode = 1 THEN 'Cash' ELSE 'Other' END AS PaymentMethod,
	sum(paymentamount) as PaymentAmount
from @demo
GROUP BY PaymentCode

Giving

PaymentMethod	PaymentAmount
Cash	400.40
Other	600.60