SQL Server MSSQL Monthly statistics all departments

Source: Internet
Author: User

Take order statistics for example, front-end display histogram (jquery statistics):

The table and the main fields are described below; Table name: Orders
1. Date Createtime
2. Amount Amount
3. User UserID

Situation One:
According to the Department statistics of a year monthly sales (query a department monthly statistics)

1) directly in the SQL statement to determine the monthly information, benefits, direct call from the foreground, the disadvantage, performance is not high.

SQL statements:

SELECT
SUM (case if MONTH (s.createtime) = 1 then s.amount ELSE 0 END) as ' January ',
SUM (case if MONTH (s.createtime) = 2 then s.amount ELSE 0 END) as ' February ',
SUM (case if MONTH (s.createtime) = 3 then S.amount ELSE 0 END) as ' March ',
SUM (case if MONTH (s.createtime) = 4 then s.amount ELSE 0 END) as ' April ',
SUM (case if MONTH (s.createtime) = 5 then s.amount ELSE 0 END) as ' May ',
SUM (case if MONTH (s.createtime) = 6 then S.amount ELSE 0 END) as ' June ',
SUM (case if MONTH (s.createtime) = 7 then S.amount ELSE 0 END) as ' July ',
SUM (case if MONTH (s.createtime) = 8 then S.amount ELSE 0 END) as ' August ',
SUM (case if MONTH (s.createtime) = 9 then s.amount ELSE 0 END) as ' September ',
SUM (case when MONTH (s.createtime) = Ten then S.amount ELSE 0 END) as ' October ',
SUM (case when MONTH (s.createtime) = All then S.amount ELSE 0 END) as ' November ',
SUM (case when MONTH (s.createtime) = s.amount ELSE 0 END) as ' December '
From Orders as S
WHERE year (s.createtime) = 2014
--Other conditions
Results:
January February April May June seven month August Borlnd bodyguards November December
0.000.000.000.000.000.000.000.000.00741327.00120505.000.00

2) Statistics of the value of the month in the database, and then the front-end logic to judge the other months complement 0

SQL statements:

SELECT
Userid
Month (createtime) as months,
SUM (Amount) as statistics
From
Orders
WHERE
Year (Createtime) = 2014--This assumes that you want to check the monthly statistics for 2014 years.
--Other conditions
GROUP by
UserID, MONTH (createtime)
Month Sales
10741327.00
11120505.00

Situation Two:
Statistics of monthly sales for all departments in one year

1) This large amount of data impact performance, SQL statements (not found in the Department table):
SELECT
Userid
SUM (case if MONTH (s.createtime) = 1 then s.amount ELSE 0 END) as ' January ',
SUM (case if MONTH (s.createtime) = 2 then s.amount ELSE 0 END) as ' February ',
SUM (case if MONTH (s.createtime) = 3 then S.amount ELSE 0 END) as ' March ',
SUM (case if MONTH (s.createtime) = 4 then s.amount ELSE 0 END) as ' April ',
SUM (case if MONTH (s.createtime) = 5 then s.amount ELSE 0 END) as ' May ',
SUM (case if MONTH (s.createtime) = 6 then S.amount ELSE 0 END) as ' June ',
SUM (case if MONTH (s.createtime) = 7 then S.amount ELSE 0 END) as ' July ',
SUM (case if MONTH (s.createtime) = 8 then S.amount ELSE 0 END) as ' August ',
SUM (case if MONTH (s.createtime) = 9 then s.amount ELSE 0 END) as ' September ',
SUM (case when MONTH (s.createtime) = Ten then S.amount ELSE 0 END) as ' October ',
SUM (case when MONTH (s.createtime) = All then S.amount ELSE 0 END) as ' November ',
SUM (case when MONTH (s.createtime) = s.amount ELSE 0 END) as ' December '
From Orders as S
WHERE year (s.createtime) = 2014
GROUP BY UserID

Results:
UserID January February April May June seven month August Borlnd bodyguards November December
10.000.000.000.000.000.000.000.000.000.0053495.000.00
20.000.000.000.000.000.000.000.000.00738862.0037968.000.00
30.000.000.000.000.000.000.000.000.002099.0022849.000.00
40.000.000.000.000.000.000.000.000.00366.000.000.00
50.000.000.000.000.000.000.000.000.000.006193.000.00

2) Baidu saw someone mention the list of career change, did not see the instance, not very clear concrete implementation way. Have a friend to know, please inform, thank you!
SELECT
Userid
Month (createtime) as months,
SUM (Amount) as statistics
From
Orders
WHERE
Year (Createtime) = 2014--This assumes that you want to check the monthly statistics for 2014 years.
GROUP by
Userid,month (Createtime)

Results:
UserID Month Statistics
110738862.00
2102099.00
310366.00
41153495.00
11137968.00
21122849.00
5116193.00

SQL Server MSSQL Monthly statistics all departments

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.