Remove group by? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Remove group by?

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-07-02 : 14:23:43

Hi,
I have a script

select A.TRAN_CD,A.TRAN_ID, T.TND_DESCR, 
case when E.TND_AMT < 0 THEN SUM(E.TND_AMT*-1) else sum(E.TND_AMT) END AS ENTERED_AMOUNT, SUM(E.AMT*-1) AS TOTAL_TENDER, A.RGST_TRAN_NBR
FROM TRANS_HEADER A
JOIN TRANSACTION_TENDER E ON E.TRAN_ID = A.TRAN_ID left join TENDER T on T.TND_CD = E.TND_CD
 WHERE  A.VOID_CD = 0 AND E.VOID_CD = 0 AND A.TRNING_MDE_FG = 0 and A.TRAN_CD =1 AND E.TRAN_ID=5375

GROUP BY A.TRAN_CD,A.TRAN_ID,E.TND_CD, E.TND_AMT,T.TND_DESCR, A.RGST_TRAN_NBR

It works the result is

1	5375	Cash	100.00	100.00	2670
1	5375	Cash	37.00	-37.0	2670
1	5375	VISA	50.00	50.00	2670

But I want to get

1	5375	Cash	100.00	63	2670
1	5375	VISA	50.00	50.00	2670

63=100-37
If I remove the E.TND_AMT from group by an error will occur, so what is the right query?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-02 : 14:31:06

Yeah, you need to lose at least one of the group bys: try chaning:

case when E.TND_AMT < 0 THEN SUM(E.TND_AMT*-1) else sum(E.TND_AMT) END AS ENTERED_AMOUNT

MAX(case when E.TND_AMT < 0 THEN E.TND_AMT*-1 else E.TND_AMT END) AS ENTERED_AMOUNT

But, it'd probalby be more helpfull if you can supply do some DDL work to set up the data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-07-02 : 14:50:45

Thanks for your input.
But if the original question becomes


1	5375	Cash	50.00	50.00	2670
1	5375	Cash	50.00	50.00	2670
1	5375	Cash	37.00	-37.0	2670
1	5375	VISA	50.00	50.00	2670

I want to get the same result, how to
Using your sql will result


1	5375	Cash	50.00	63	2670
1	5375	VISA	50.00	50.00	2670

50+50-37=63
Notice

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-02 : 15:03:19

If you want help read the link I posted on 07/02/2009 14:31:06 and supply: DDL, Sample data and expected output in a sql ready format.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-07-02 : 16:01:33

Okay. I am sorry that I don't have dll because it is complex but I have some sample data.
Skip unnesscessary columns.
Table TRANS_HEADER[CODE]
TRAN_CD TRAN_ID
1 5375
1 5376
1 5377
98 5378 [/CODE]
Table TRANSACTION_TENDER[CODE]
TRAN_ID AMT TND_AMT TND_CD
5375 -40.0000 -40.0000 1
5375 -60.0000 -60.0000 1
5375 37.0000 37.0000 1
5376 -15.1200 -15.1200 1
5377 -112.9900 -112.9900 5
[/CODE]

TENDER TABLE[CODE]
TND_CD TND_DESCR
1 Cash
5 VISA[/CODE]
What I want to get[CODE]
TRAN_ID DESCR ENTER_AMOUNT TENDER_AMOUNT
5375 Cash 100.00 63
5375 VISA 50.00 50.00
[/CODE]
Let me try some dll
[CODE]
create table TRANS_HEADER(TRAN_CD int,TRAN_ID int)
insert into TRANS_HEADER(TRAN_ID,TRAN_ID)
values(1,5375)
insert into TRANS_HEADER(TRAN_ID,TRAN_ID)
values(1,5376)
insert into TRANS_HEADER(TRAN_ID,TRAN_ID)
values(1,5377)
insert into TRANS_HEADER(TRAN_ID,TRAN_ID)
values(98,5378)
create TRANSACTION_TENDER(TRAN_ID int,AMT float(8),TND_AMT float(8),TND_CD int)
insert into TRANSACTION_TENDER(TRAN_ID,AMT,TND_AMT,TND_CD)
values(5375, -40.0000, -40.0000, 1)
insert into TRANSACTION_TENDER(TRAN_ID,AMT,TND_AMT,TND_CD)
values(5375, -60.0000, -60.0000, 1)
insert into TRANSACTION_TENDER(TRAN_ID,AMT,TND_AMT,TND_CD)
values(5375, 37.0000, 37.0000, 1)
insert into TRANSACTION_TENDER(TRAN_ID,AMT,TND_AMT,TND_CD)
values(5376, -15.1200, -15.1200, 1)
insert into TRANSACTION_TENDER(TRAN_ID,AMT,TND_AMT,TND_CD)
values(5377, -112.9900, -112.9900, 5)
create TENDER(TND_CD int,TND_DESCR)
insert into TENDER(TND_CD,TND_DESCR)
values(1,'CASH')
insert into TENDER(TND_CD,TND_DESCR)
values(5,'VISA')[/CODE]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-02 : 16:31:43

I think I get how you arrive at the CASH amounts, but how do you get VISA amounts?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-02 : 16:44:36

I'm guess there is a data problem withyour sample data. try this:

select 
	A.TRAN_CD,
	A.TRAN_ID, 
	T.TND_DESCR, 
	SUM(case when E.TND_AMT < 0 THEN E.TND_AMT*-1 else 0 END) AS ENTERED_AMOUNT, 
	SUM(E.AMT*-1) AS TOTAL_TENDER
FROM 
	TRANS_HEADER A
JOIN 
	TRANSACTION_TENDER E 
	ON E.TRAN_ID = A.TRAN_ID 
left join 
	TENDER T 
	on T.TND_CD = E.TND_CD
 WHERE  
	A.TRAN_CD =1 
	AND E.TRAN_ID=5375
GROUP BY 
	A.TRAN_CD,
	A.TRAN_ID,
	T.TND_DESCR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources