grouping when column contains NULL

Question

I have the below query:

SELECT  VoucherId, 
        PosDateEntered, 
        SUM(VoucherFaceValue) AS ValueIssued, 
        SUM(AmountUsed) AS ValueReedeemed, 
        RVtransactionAmount    
FROM [dbo].[LoyaltyVoucherTransactionDetails]
WHERE  VoucherId = '2000702' 
GROUP BY PosDateEntered, VoucherId, RVtransactionAmount

That returns the below data:

How can I then make this into the below result?

I am using SSMS 2014

Not quite sure what you "below result" is. Looks like you have all the values from the second row in your actual results except RVtransactionAmount, which seems to be from the first row. — CodeJockey, Jun 29 '16 at 13:59
Just to add I need to show the RVtransactionAmount of -5 but am trying to bring it all into one line but the NULL is stopping this.... — PIPRON79, Jun 29 '16 at 14:00
Why do you want the RVtransactionAmount to be -5, instead of 5? What is the rule that needs to be followed here? — Tab Alleman, Jun 29 '16 at 14:08

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

I am not too sure, but I think you will need ISNULL and NOT. You could try this: VoucherId = '2000702' AND PosDateEntered!= NULL

If you need anymore help this might help: SQL - don't display row when two criteria are met

edited May 23 '17 at 10:28

Community

1
1

answered Jun 29 '16 at 13:55

Alex

1

Wow how did I think that. Yeah you will need "IS NOT NULL" instead of "!= NULL" – Alex Jun 29 '16 at 13:56
but this then gives me a result with RVtransactionAmount -5 which I still need – PIPRON79 Jun 29 '16 at 14:02
You will need to do some sort of join to, but probably have to make a new table for that. Look up left full joins. – Alex Jun 29 '16 at 14:16

score 0 · Answer 2 · answered Jun 29 '16 at 20:37

Here is my solution.

create table test
(
    voucherid int,
    posdateentered datetime,
    valueissued int,
    valueredeemed int,
    rvtransactionamount decimal(9,3)
)

insert into test values (2000702, null, 5, 0, -5.00)
insert into test values (2000702, '4/2/2016', 5, 0, 5.00)

select distinct t1.voucherid, t2.posdateentered,
    t1.valueredeemed, t1.rvtransactionamount
from test t1
left join test t2 on t1.voucherid = t2.voucherid 
where t2.posdateentered is not null

grouping when column contains NULL

2 Answers2