SQL SERVER: Open window function SUM () over () data statistics in one case using

Source: Internet
Author: User

Due to the previous period of stomach pain, the blog Park blog has been stopped more than one months or so. In recent days, stomach trouble finally slightly improved, decided to re-write blog post.

A few days ago, a friend just asked me about the SQL statement, roughly the original table has two columns, respectively, the month, the month sales, and the need for a SQL statement to achieve the statistics of each month and the current month before the sales and the month. No, it's still not clear, the following figure is shown.

   

SQL Test Table Script

DECLARE @Temp Table (  ID INT,---month  moneydata Float  ---amount) insert into  @TEMPSELECT 1,100 UNION allselect< C7/>2,200 Union allselect  3,300 Union allselect  4,400 Union allselect  5,500 Union allselect  6,600 UNION Allselect  7,600

A self-connected

SELECT  a.id, SUM (b.moneydata) from  @Temp A INNER JOIN @Temp BON a.id>=b.id GROUP by a.id------focus on the on condition, via self-connect a.ID >= b.ID, can obtain the required data, and then through group by, sum can be summed

  

Two open Window function

SELECT  ID, Moneydata,   sum (moneydata) over () as ' total sales ', sum (moneydata) over (PARTITION by ID) as ' monthly Sales ', SUM (money Data) over (order by ID ASC)  as ' month + previous month sales ' from @Temp----here, using the window function is simpler, however, where SUM () over (ORDER by ID ASC) can achieve the above Function

  

For the specific use of the window function, refer to Microsoft MSDN Official API documentation, and finally, recommend a window function book: SQL SERVER 2012 based Windows functions.

Reference: Https://msdn.microsoft.com/zh-cn/library/ms173454.aspx

SQL SERVER: Open window function SUM () over () data statistics in one example using

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.