Click here to Skip to main content
14,978,764 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have this current sql query

image — ImgBB[^]

 SELECT
 CASE
       WHEN StoreID = 1 THEN 'CWM'
       WHEN StoreID = 2 THEN 'CWD' END as accountcode,
 DocEntry,PaymentCode,
 case when PaymentCode <> 1 then paymentamount end as OtherPaymentAmount,
 case when PaymentCode = 1 then paymentamount end as CashAmount,


 tenders.sapcreditcard AS sapcreditcard,

paymentamount,

-- sum (case when PaymentCode >= 1 then paymentamount else NULL end) as Total,
 FileName, BPA_ProcessStatus, ERP_PaymentProcessed

 FROM [Plu].[dbo].[payments_header] LEFT JOIN tenders ON payments_header.PaymentCode = tenders.postenderid
   WHERE BPA_ProcessStatus='N' and ERP_PaymentProcessed='N'
 group by PaymentCode, paymentamount, docentry, storeid,sapcreditcard, FileName, BPA_ProcessStatus,ERP_PaymentProcessed


What im looking for is, to sum all the items with same payment code, but leaving all the others columns too, as i need them with my integration,
i tried that group by but still not working. What im missing?

current results:

accountcode	DocEntry	PaymentCode	OtherPaymentAmount	CashAmount	sapcreditcard	BPA_ProcessStatus	ERP_PaymentProcessed
CWM	238	1	NULL	0.29	NULL	N	N
CWM	238	1	NULL	29.00	NULL	N	N
CWM	238	1	NULL	29.58	NULL	N	N
CWM	238	9	18.00	NULL	4	N	N


expected result:

accountcode	DocEntry	PaymentCode	OtherPaymentAmount	CashAmount	sapcreditcard	BPA_ProcessStatus	ERP_PaymentProcessed
CWM	238	1	NULL	58.87	NULL	N	N
CWM	238	9	18.00	NULL	4	N	N


What I have tried:

 SELECT
 CASE
       WHEN StoreID = 1 THEN 'CWM'
       WHEN StoreID = 2 THEN 'CWD' END as accountcode,
 DocEntry,PaymentCode,
 case when PaymentCode <> 1 then paymentamount end as OtherPaymentAmount,
 case when PaymentCode = 1 then paymentamount end as CashAmount,


 tenders.sapcreditcard AS sapcreditcard,

paymentamount,

-- sum (case when PaymentCode >= 1 then paymentamount else NULL end) as Total,
 FileName, BPA_ProcessStatus, ERP_PaymentProcessed

 FROM [Plu].[dbo].[payments_header] LEFT JOIN tenders ON payments_header.PaymentCode = tenders.postenderid
   WHERE BPA_ProcessStatus='N' and ERP_PaymentProcessed='N'
 group by PaymentCode, paymentamount, docentry, storeid,sapcreditcard, FileName, BPA_ProcessStatus,ERP_PaymentProcessed
Posted
Updated 7-May-21 2:28am
v3
Comments
CHill60 7-May-21 5:16am
   
You are more likely to get an answer to your question if you do the following:
1 - Provide us with the schema for tables [payments_header] and [tenders]
2 - Provide Sample data for both tables
3 - Provide the expected results for the sample data provided
Don't give us links to pictures outside this site - many of us are at work and such sites are blocked. Apart from that we can't cut and paste pictures into an IDE.
We do this in our spare time so few people will bother to transcribe your picture into actual data or code, but if you follow my suggestions above, then many more will just copy & paste it to try to find a solution for your problem.
In other words, the better you present your question the more likely you are to get a useful response

1 solution

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:
SQL
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 ...
SQL
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:
SQL
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
SQL
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
SQL
group by PaymentCode,StoreID
In your case it looks like you carried on just adding the columns as they were reported as errors
SQL
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
SQL
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
   
Comments
Alexis Gaitan 7-May-21 8:12am
   
what about im interested in other columns? like Paymentcode and StoreID
Alexis Gaitan 7-May-21 8:13am
   
at least im needing this:

accountcode DocEntry PaymentCode OtherPaymentAmount CashAmount sapcreditcard BPA_ProcessStatus ERP_PaymentProcessed
CWM 238 1 NULL 0.29 NULL N N
CWM 238 1 NULL 29.00 NULL N N
CWM 238 1 NULL 29.58 NULL N N
CWM 238 9 18.00 NULL 4 N N

i edited my question so to show this expected result:

accountcode DocEntry PaymentCode OtherPaymentAmount CashAmount sapcreditcard BPA_ProcessStatus ERP_PaymentProcessed
CWM 238 1 NULL 58.87 NULL N N
CWM 238 9 18.00 NULL 4 N N
Maciej Los 7-May-21 8:14am
   
5ed!
Alexis Gaitan 7-May-21 8:27am
   
I modified my question, as now i need yes or yes a cashamount and otherpayment columns, in order to integrate later with my systems.
Alexis Gaitan 7-May-21 8:43am
   
nevermind i just got it from your query i made some modifications and works perfect, thank you very much
CHill60 7-May-21 9:50am
   
Excellent news! Glad I could help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900