How to do the subtract function in sql script

Question

I need to write a sql script which is used to calculate our customer left points after redemption at the beginning of each month.But I am not very sure about the difference between SQL query and SQL script. I wrote the two queries to calculate the points earned and spent. For total points earned:

   select Tbl_member.member_id,sum(points_earned) 
   from Tbl_member, Tbl_member_participation
   where tbl_member_participation.member_id = Tbl_member.member_id
   group by Tbl_member.member_id,

and points spent:

  select Tbl_member.member_id,sum(points_redeemed)
  from Tbl_member, Tbl_member_redemption
  where tbl_member_redemption.member_id = Tbl_member.member_id
  group by Tbl_member.member_id

I want to subtract spent points from earned points, but I don't know how to combine it into a signle output sql script. And how to make this run for every month.

I believe @Edirich wants you to specify if you are running MySQL, Oracle, SQL Server etc. — Vojtěch Dohnal, Oct 29 '14 at 05:18

score 1 · Answer 1 · answered Oct 29 '14 at 05:06

1

You can simply use UNION function to combine two query results. This query is not tested but these is the the way that you can simply play with two queries by using UNION.

 SELECT
    member_id,
    SUM(points_earned) - SUM(points_redeemed)
FROM
(
   SELECT 
       Tbl_member.member_id,
       sum(points_earned) points_earned,
       0 AS  points_redeemed 
   FROM 
       Tbl_member, 
       Tbl_member_participation
   WHERE 
    tbl_member_participation.member_id = Tbl_member.member_id
   GROUP BY
    Tbl_member.member_id


   UNION ALL

     select 
        Tbl_member.member_id,
        0 AS points_earned,
        sum(points_redeemed) points_redeemed
    from 
        Tbl_member, Tbl_member_redemption
    where 
        tbl_member_redemption.member_id = Tbl_member.member_id
    group by 
        Tbl_member.member_id

  ) as
 a
 GROUP BY
 member_id

answered Oct 29 '14 at 05:06

Hardik Parmar

1,053
3
15
39

@xiaopassion Please reply me if these query is giving any errors – Hardik Parmar Oct 29 '14 at 05:12
Thank you~ but how to make sure this script can run for every month's record? and why need" 0 AS points_redeemed "? – xiaopassion Oct 29 '14 at 05:13
Coz you need same number of column in both the query then only union will work that is why i took 0 as points_redeemed.for further reference http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Hardik Parmar Oct 29 '14 at 05:20
@xiaopassion For basics of union you will find this very useful link http://www.w3schools.com/sql/sql_union.asp – Hardik Parmar Oct 29 '14 at 05:21
@xiaopassion was my query executed properly & satisfied all your need]? – Hardik Parmar Oct 29 '14 at 05:39
I still confused though, which is after run the union,there will be two rows in the after-union table right? so the SUM(points_earned) - SUM(points_redeemed) will comes out two result? – xiaopassion Oct 29 '14 at 05:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/63836/discussion-between-hardik-parmar-and-xiaopassion). – Hardik Parmar Oct 29 '14 at 05:50

Vojtěch Dohnal · Answer 2 · 2014-10-29T06:58:22.217

You are doing CROSS JOIN which is not what you intend (from Tbl_member, Tbl_member_participation). Use LEFT JOIN or INNER JOIN instead.

SQL Script can contain multiple queries and other commands like FOR etc. and can contain also GO statements. From your perspective it is not really important.

  SELECT m.member_id, SUM(mr.points_redeemed), SUM (mp.points_earned), 
         ISNULL(SUM(mp.points_earned),0) - ISNULL(SUM(mr.points_redeemed),0) as Result
  FROM Tbl_member m
  LEFT JOIN  Tbl_member_redemption mr ON mr.member_id = m.member_id
  LEFT JOIN  Tbl_member_participation mp ON mp.member_id = m.member_id
  GROUP BY m.member_id

You can achieve your goal with one query. If you want results for individual months, just add a criteria to your GROUP BY like this:

GROUP BY m.member_id, MONTH(nameOfDateColumn), YEAR(nameOfDateColumn)

Pரதீப் · Accepted Answer · 2014-10-29T05:30:33.873

1

Try this

SELECT Tbl_member.member_id,
       Sum(points_earned) - Sum(points_redeemed) Remaining_points
FROM   Tbl_member
       JOIN Tbl_member_participation
         ON tbl_member_participation.member_id = Tbl_member.member_id
       JOIN Tbl_member_redemption
         ON tbl_member_redemption.member_id = Tbl_member.member_id
GROUP  BY Tbl_member.member_id,
          Datepart(mm, datecol),
          Datepart(yyyy, datecol)

edited Oct 29 '14 at 05:30

answered Oct 29 '14 at 05:25

Pரதீப்

91,748
19
131
172

And where is `Tbl_member_redemption` table? – Vojtěch Dohnal Oct 29 '14 at 05:28
ya missed it and added now – Pரதீப் Oct 29 '14 at 05:31
And what if the client did not redeem any points and only earned points? – Vojtěch Dohnal Oct 29 '14 at 05:31
Client who redeemed will only be joined!! Tbl_member_redemption will have data only if a client makes a transaction!! question is to find Remaining points!! – Pரதீப் Oct 29 '14 at 05:32

score 1 · Answer 4 · answered Oct 29 '14 at 05:54

if you are not concerned that query should show rows for all member then you can remove join of member table like

SELECT p.member_id,
       Sum(points_earned),
       Sum(points_redeemed),
       Sum(CASE
             WHEN points_earned IS NULL THEN 0
             ELSE points_earned
           END) - Sum(CASE
                        WHEN points_redeemed IS NULL THEN 0
                        ELSE points_redeemed
                      END) AS diff
FROM   Tbl_member_participation p
       LEFT OUTER JOIN tbl_member_redemption r
                    ON p.member_id = r.member_id
GROUP  BY p.member_id

otherwise use this :

SELECT m.member_id,
       Sum(points_earned),
       Sum(points_redeemed),
       Sum(CASE
             WHEN points_earned IS NULL THEN 0
             ELSE points_earned
           END) - Sum(CASE
                        WHEN points_redeemed IS NULL THEN 0
                        ELSE points_redeemed
                      END) AS diff
FROM   Tbl_member m
       LEFT OUTER JOIN Tbl_member_participation p
                    ON m.member_id = p.member_id
       LEFT OUTER JOIN tbl_member_redemption r
                    ON m.member_id = r.member_id
GROUP  BY m.member_id ;

and if you want to find the difference month-wise then

SELECT m.member_id,datepart(mm,p.earndate),datepart(yyyy,p.earndate),
       Sum(points_earned),
       Sum(points_redeemed),
       Sum(CASE
             WHEN points_earned IS NULL THEN 0
             ELSE points_earned
           END) - Sum(CASE
                        WHEN points_redeemed IS NULL THEN 0
                        ELSE points_redeemed
                      END) AS diff
FROM   Tbl_member m
       LEFT OUTER JOIN Tbl_member_participation p
                    ON m.member_id = p.member_id 
       LEFT OUTER JOIN tbl_member_redemption r
                    ON m.member_id = r.member_id and p.earndate = r.redeemdate
GROUP  BY m.member_id,datepart(mm,p.earndate),datepart(yyyy,p.earndate)

here is a sql fiddle demo

There is an ISNULL function in SQL Server. Also when you SUM values and some of them are NULL you do not need to use ISNULL. — Vojtěch Dohnal, Oct 29 '14 at 06:01
Thank you for noticing that, 'isnull' can be used in place of 'case when', 'coalesce' is also another option. But, suppose, there are points_earned and no point_redeem (in that month) then the difference will come as NULL that's why I added it and maybe such situation is not possible when using actual DB. this can elaborate more : http://sqlfiddle.com/#!3/891e3/3. see the difference in two queries — Deep, Oct 29 '14 at 06:44
Actually you put some work elaborating this answer, so +1. But the part `ON m.member_id = r.member_id and p.earndate = r.redeemdate` would not work as it should. — Vojtěch Dohnal, Oct 29 '14 at 06:59

How to do the subtract function in sql script

4 Answers4