Summary of commonly used data statistics SQL

Ext.: http://www.cnblogs.com/zhangweizhong/p/5577842.html

Recently in a BI project, the need for a large number of SQL data statistics related to the application, deepened my understanding and use of SQL.

So, a summary of the SQL statements commonly used to share several data statistics:

1. Statistical data under various conditions

Selectbatchid,sum (cardsum) total amount, sum (case when Status=1 then cardsum else 0 end) as used, sum (case when status=2 then Cardsum else 0 end) as has been frozen from the Giftcard group by BatchId

2. Statistics of daily, monthly and yearly data

Select year (addtime), month (addtime) month, day (addtime), COUNT (1) Quantity, sum (cardsum) sales total from Giftcardgroup by year (Addtime) , month (addtime), Day (Addtime)

3. A column to redo statistics

Select count (BatchId), COUNT (distinct BatchId), COUNT (distinct batchname) from Giftcard

4. Row to Column

Select *from (    select         batchname,         cardsum as Totamount from     Giftcard) as Spivot (        SUM (Totamount)    for Batchname in (ZX Test product, Test newcomer coupon, testing college entrance exam)) As Mypivot

5. Get the smallest unused ID number in the table

Select (Case if EXISTS (SELECT * from Giftcard b WHERE b.id = 1) then MIN (Id) + 1 ELSE 1 END) as-Id from Giftcardwhere NO T Id in (SELECT a.id-1 from Giftcard a)

6. Query for the number of data that is not duplicated for a column

Where NOT EXISTS (select 1 from Giftcard where Batchname=a.batchname and Id<a.id)

7. Annual statistics from January to 12 months sales

Select year (addtime) as ' years ', sum (case if month (addtime) =1 then cardsum else 0 end) as ' January ', sum (case when month (addtime) = 2 then cardsum else 0 end) as ' February ', sum (case when month (addtime) =3 then cardsum else 0 end) as ' March ', sum (case when month (A  Ddtime) =4 then cardsum else 0 end) as ' April ', sum (case when MONTH (addtime) =5 then cardsum else 0 end) as ' May ', sum Month (addtime) =6 then cardsum else 0 end) as ' June ', sum (case when MONTH (addtime) =7 then cardsum else 0 end) as ' July ', sum (c ASE when month (addtime) =8 then cardsum else 0 end) as ' August ', SUM (case if month (addtime) =9 then cardsum else 0 end) as ' IX Month ', sum (case when month (addtime) =10 then cardsum else 0 end) as ' October ', sum (case when month (addtime) =11 then cardsum else 0 E nd) as ' November ', SUM (case if MONTH (addtime) =12 then cardsum else 0 end) as ' December ' from Giftcardgroup by year (Addtime)

Summary of commonly used data statistics SQL (GO)