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