CODEWITHSUNDEEP
asp.netsql-server
Siraj Ahmed
Siraj Ahmed

Reputation: 3

how to sum the column and get the rows based on month and year

It is my table can i get result for this . Each user have points,i have to show number of user points got in current month,current year

Thanks.

--------------------------------
| userId | points | date       |
--------------------------------
| 1      | 5      | 8/25/2013  |
| 1      | 3      | 8/16/2013  |
| 1      | 2      | 8/01/2013  |
| 1      | 2      | 9/25/2013  |
| 1      | 5      | 8/25/2013  |
| 1      | 3      | 2/16/2012  |
| 2      | NULL   | NULL       |
| 2      | NULL   | NULL       |
--------------------------------

They result should be like :

---------------------------------------------------
| userId | CurrentMonthpoints | CurrentYearPoints |
---------------------------------------------------
| 1      | 15                 | 17                |
| 2      | NULL               | NULL              |
---------------------------------------------------

My request :

SELECT  userId, 
        (SELECT   sum(points)
            from tbl_points
            WHERE  PointsDate between '8/1/2013' and '8/31/2013') AS CurrentMonthPoints,
        (SELECT distinct SUM(points)
             from tbl_points
             WHERE PointsDate between '1/1/2014' and '12/31/2014' ) AS CurrentYearPoints
 from tbl_user_performance_points

But My query shows wrongly as :

---------------------------------------------------
| userId | CurrentMonthpoints | CurrentYearPoints |
---------------------------------------------------
| 1      | 15                 | 17                |
| 2      | 15                 | 17                |
---------------------------------------------------

Advance Thanks

Upvotes: 0

Views: 105

Answers (3)

Tricky12
Tricky12

Reputation: 6822

Give this a try:

select a.userID,
  a.points_sum_m as currentmonthpoints,
  b.points_sum_y as currentyearpoints
from (select userID, sum(points) as points_sum_m
   from tbl_points
   where month(date) = month(getdate())
   group by userID) a
inner join (select userID, sum(points) as points_sum_y
   from tbl_points
   where year(date) = year(getdate())
   group by userID) b
on a.userID = b.userID;

SQLFiddle: http://sqlfiddle.com/#!3/ff780/12

Upvotes: 1

r.net
r.net

Reputation: 631

if you just want to correct your query replace it with this...

SELECT  userId, 
        (SELECT   sum(points)
            from tbl_points
            WHERE  PointsDate between '8/1/2013' and '8/31/2013' and userId= X.UserId) AS CurrentMonthPoints,
        (SELECT distinct SUM(points)
             from tbl_points
             WHERE PointsDate between '1/1/2014' and '12/31/2014' and userId = X.UserId) AS CurrentYearPoints
 from tbl_user_performance_points X

Upvotes: 0

Sonam
Sonam

Reputation: 3466

  with month_cte(userid,Current_Month) as
  (select userid,sum(Points)Current_Month from tbl_points
  where datepart(mm,PointsDate)=datepart(mm,getdate()) and datepart(YY,PointsDate)=datepart(yy,getdate()) group by userid)

   , year_cte(userid,Current_year) as
  (select userid,sum(Points)Current_Year from tbl_points
  where datepart(YY,PointsDate)=datepart(yy,getdate()) group by userid)

  select Distinct t.Userid, Current_Month, Current_Year From tbl_points T 
  left join month_cte mc on T.userid=mc.userid
  left join year_cte yc on t.userid=yc.userid

Upvotes: 0

Related Questions