A subtraction of two sums in the same table

Well, I have been trying to do something and maybe the heat maybe the lack of concentration I cannot fully get it to work.

I have ONE table, whereby in order to calculate the actual total, a simple sum won't do. Instead, I need to get two sub-sums and subtract them. The trick here is that I want to do this using just ONE query. I can easily get the two sums using two different queries, but an attempt to it all using just one query has failed.

The first query gets the total for everything but when transaction type is '2', while the second query as you can see gets the sum for when transaction type is equal to 2. Now what's elft is putting the two together.

SELECT            lcPoints.LOYALTY_CARD_OID AS LC_OID, SUM(lcPoints.NUMBER_OF_POINTS) AS Points
FROM            UGS_LOYALTY_CARD_POINT_TRANSACTIONS lcPoints
WHERE            lcPoints.TRANSACTION_TYPE <> 2
GROUP BY      lcPoints.LOYALTY_CARD_OID

SELECT            lcPoints.LOYALTY_CARD_OID AS LC_OID, SUM(lcPoints.NUMBER_OF_POINTS) AS Points
FROM            UGS_LOYALTY_CARD_POINT_TRANSACTIONS lcPoints
WHERE            lcPoints.TRANSACTION_TYPE = 2
GROUP BY      lcPoints.LOYALTY_CARD_OID


I tried the following:

SELECT            lcPoints2.LOYALTY_CARD_OID AS LC_OID, SUM(COALESCE(lcPoints2.NUMBER_OF_POINTS, 0)-COALESCE(p2.Points, 0))
FROM            UGS_LOYALTY_CARD_POINT_TRANSACTIONS lcPoints2 LEFT OUTER JOIN
                  (
                        SELECT            lcPoints.LOYALTY_CARD_OID AS LC_OID, SUM(lcPoints.NUMBER_OF_POINTS) AS Points
                        FROM            UGS_LOYALTY_CARD_POINT_TRANSACTIONS lcPoints
                        WHERE            lcPoints.TRANSACTION_TYPE = 2
                        GROUP BY      lcPoints.LOYALTY_CARD_OID
                  ) p2 ON p2.LC_OID= lcPoints2.LOYALTY_CARD_OID
WHERE            lcPoints2.TRANSACTION_TYPE <> 2
GROUP BY      lcPoints2.LOYALTY_CARD_OID

And it doesn't work. (I tried a couple of other things as well and they didn't work)
LVL 7
Gene_CypAsked:
Who is Participating?
 
gothamiteCommented:
Can you try this?
SELECT            lcPoints.LOYALTY_CARD_OID AS LC_OID, SUM(CASE WHEN lcPoints.TRANSACTION_TYPE=2 THEN lcPoints.NUMBER_OF_POINTS ELSE -1*lcPoints.NUMBER_OF_POINTS END) AS Points
FROM            UGS_LOYALTY_CARD_POINT_TRANSACTIONS lcPoints
WHERE            lcPoints.TRANSACTION_TYPE <> 2
GROUP BY      lcPoints.LOYALTY_CARD_OID

Open in new window

0
 
Gene_CypAuthor Commented:
Bare in mind a simple
query1 - query2  doesn't work because my queries above return more than one line.
0
 
Gene_CypAuthor Commented:
Hey gothamite, your query above had a couple of errors, but I was able to use it to derive the correct query (if you are wondering the where clause was not needed anymore and you said if TRANSACTION_TYPE=2 instead of <>2)
 

SELECT lcPoints.LOYALTY_CARD_OID AS LC_OID, SUM(CASE WHEN lcPoints.TRANSACTION_TYPE<>2 THEN lcPoints.NUMBER_OF_POINTS ELSE -1*lcPoints.NUMBER_OF_POINTS END) AS Points
FROM UGS_LOYALTY_CARD_POINT_TRANSACTIONS lcPoints
GROUP BY lcPoints.LOYALTY_CARD_OID
 
Thanks for the swift reply, I'll credit you the points and flag mine as a 0-point assist
0
 
Gene_CypAuthor Commented:
Thanks
0
 
Gene_CypAuthor Commented:
Looks like I messed up on flagigng more than one answer, hope people who come searching will read further down. ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.