Subtotal total-Statistics

Create Table [TB] ([Customer Code] varchar (10), [customer name] varchar (10), [quantity] INT)
Insert [TB]
Select '001', 'A', 2 Union all
Select '001', 'A', 3 Union all
Select '001', 'A', 4 union all
Select '002 ',' B ', 1 Union all
Select '002 ',' B ', 2

-- Statistics
Select * from
(Select * from TB
Union all
Select Customer Code, customer name = 'subtotal ', sum (Quantity) quantity from TB group by Customer Code
Union all
Select customer code = '', customer name = 'Total', sum (Quantity) quantity from TB
) T
Order
Case Customer Code when'' then 2 else 1 end, customer code,
Case customer name When 'subtotal 'then 2 else 1 end

Drop table TB

/*
Number of customer name Codes
-------------------------------
001 A 2
001 A 3
001 A 4
001 subtotal 9
002 B 1
002 B 2
002 subtotal 3
Total 12

(The number of affected rows is 8)
*/

 

 

 

 

 

 

Create Table Tb (date char (10), Col varchar (10 ))
Insert TB select '2017-2005 ', 'sheng'
Insert TB select '2017-2005 ', 'sheng'
Insert TB select '2017-2005 ', 'negative'
Insert TB select '2017-2005 ', 'negative'
Insert TB select '2014-05-10 ', 'sheng'
Insert TB select '2017-05-10 ', 'negative'
Insert TB select '2017-05-10 ', 'negative'

Select date
, Sum (case when Col = 'sheng' then 1 else 0 end) as [wins]
, Sum (case when Col = 'negative 'Then 1 else 0 end) as negative
From TB
Group by date

Date wins/loses
--------------------------------
2 2
1 2

(2 rows affected)