Clever Geek Handbook

Total SQL Sum by adjacent months

I have the following table (Example):

+----+--------+-------+------+------------+
| ID | WEIGHT | MONTH | YEAR | CATEGORYID |
+----+--------+-------+------+------------+
|  1 | 0.5    |     1 | 2014 | A          |
|  1 | 0.5    |     1 | 2014 | A          |
|  1 | 0.5    |     2 | 2014 | A          |
|  1 | 0.2    |     2 | 2014 | C          |
|  1 | 0.2    |     2 | 2014 | C          |
|  2 | 1.0    |     2 | 2014 | B          |
|  2 | 1.0    |     2 | 2014 | B          |
+----+--------+-------+------+------------+

The output I want would be like this: (/)> 

+----+--------+-------+------+------------+
| ID | WEIGHT | MONTH | YEAR | CATEGORYID |
+----+--------+-------+------+------------+
|  1 | 1.5    |     1 | 2014 | A          |
|  1 | 1.5    |     2 | 2014 | A          |
|  1 | 0.4    |     1 | 2014 | C          |
|  1 | 0.4    |     2 | 2014 | C          |
|  2 | 2.0    |     2 | 2014 | B          |
|  2 | 2.0    |     3 | 2014 | B          |
+----+--------+-------+------+------------+

So, when the monthly breaks, I still want to sum the weight from the previous month to the current one, etc. I want to sum the weight by a specific ID and category ID.

+3


source to share


2 answers


Hope it works.



 select DISTINCT  ID,sum(WEIGHT) over (partition by categoryid order by categoryid) as WEIGHT,     
 MONTH,YEAR, CATEGORYID 
 from table;
+1


source


try it



SELECT ID,
       Sum(s_weight)OVER(partition BY CATEGORYID, id),
       MONTH,
       YEAR,
       CATEGORYID
FROM   (SELECT ID,
               Sum(weight) AS s_weight,
               MONTH,
               YEAR,
               CATEGORYID
        FROM   Yourtable
        GROUP  BY ID,
                  MONTH,
                  YEAR,
                  CATEGORYID) a
0


source






More articles:


All Articles