Month Year Amount Customer
1     2009    100   X
1     2009    200   X
2     2009    100   X
2     2009    200   X
3     2009    100   X
3     2009    200   X

              Month
                1       2      3
Customer X     100    100    100 
Customer X     200    200    200 
TOTAL x        300    300    300 
Customer Y     200    200    200 
TOTAL Y        300    300    300 
TOTATL ALL     400    400    400  

quote:

---

Originally posted by inbs

we couldnt success do it in end application

---

Year  Month  Customer    Amount
2009   1       A1          1 
2009   1       A2          2 
2009   1       A3          3 
2009   1       B1          4  
2009   1       B2          5
2009   1       B3          6

Year  Month  Customer    Amount SUBTOTAL  TOTAL
2009   1       A1          1        6       21
2009   1       A2          2        6       21
2009   1       A3          3        6       21

2009   1       B1          4       15       21
2009   1       B2          5       15       21                   
2009   1       B3          6       15       21                   

quote:

---

Originally posted by inbs

my question is how i make subtotal for x and for y

---

DECLARE @sample TABLE
(
	[MONTH]		int, 
	[YEAR] 		int,
	Amount 		int,
	Customer	varchar(10)
)
INSERT INTO @sample
SELECT	1,     2009,    100,   'X'	UNION ALL
SELECT	1,     2009,    200,   'X'	UNION ALL
SELECT	2,     2009,    100,   'X'	UNION ALL
SELECT	2,     2009,    200,   'X'	UNION ALL
SELECT	3,     2009,    100,   'X'	UNION ALL
SELECT	3,     2009,    200,   'X'	UNION ALL
SELECT	1,     2009,    200,   'Y'	UNION ALL
SELECT	2,     2009,    200,   'Y'	UNION ALL
SELECT	3,     2009,    200,   'Y'

SELECT	Customer, [YEAR], [1], [2], [3]
FROM	(
		SELECT	Customer, [YEAR], [MONTH], Amount, 
			dense_rank = row_number() OVER (PARTITION BY Customer, [MONTH] ORDER BY [YEAR])
		FROM	@sample

		UNION ALL

		SELECT	Customer + ' TOTAL', [YEAR], [MONTH], Amount = SUM(Amount), 
			dense_rank = 1
		FROM	@sample		
		GROUP BY Customer, [YEAR], [MONTH]


		UNION ALL

		SELECT	'TOTAL ALL', [YEAR], [MONTH], Amount = SUM(Amount), 
			dense_rank = 1
		FROM	@sample		
		GROUP BY Customer, [YEAR], [MONTH]
	) d
	pivot
	(
		SUM(Amount)
		FOR [MONTH] IN ([1], [2], [3])
	) p
ORDER BY CASE WHEN Customer = 'TOTAL ALL' THEN 2 ELSE 1 END, Customer

/*
Customer         YEAR        1           2           3           
---------------- ----------- ----------- ----------- ----------- 
X                2009        100         100         100
X                2009        200         200         200
X TOTAL          2009        300         300         300
Y                2009        200         200         200
Y TOTAL          2009        200         200         200
TOTAL ALL        2009        500         500         500

(6 row(s) affected)
*/

DECLARE @sample TABLE
(
	[YEAR] 		int,
	[MONTH]		int, 
	Customer	varchar(10),
	Amount 		int
)
INSERT INTO @sample
SELECT	2009,   1,       'A',        1  UNION ALL
SELECT	2009,   1,       'A',        2  UNION ALL
SELECT	2009,   1,       'A',        3  UNION ALL
SELECT	2009,   1,       'B',        4  UNION ALL
SELECT	2009,   1,       'B',        5  UNION ALL
SELECT	2009,   1,       'B',        6

SELECT	s.[YEAR], s.[MONTH], s.Customer, s.Amount, 
	SubTotal	= SUM(Amount) OVER (PARTITION BY s.Customer),
	Total		= SUM(Amount) OVER ()
FROM	@sample s

/*
YEAR        MONTH       Customer   Amount      SubTotal    Total       
----------- ----------- ---------- ----------- ----------- ----------- 
2009        1           A          1           6           21
2009        1           A          2           6           21
2009        1           A          3           6           21
2009        1           B          4           15          21
2009        1           B          5           15          21
2009        1           B          6           15          21

(6 row(s) affected)
*/

quote:

---

Originally posted by waterduck

.... sifu =.=

---

Hope can help...but advise to wait pros with confirmation...

---

SELECT	s.[YEAR], s.[MONTH], s.Customer, s.Amount, 
	SubTotal, Total
FROM	@sample s
	CROSS APPLY
	(
		SELECT	SubTotal = SUM(Amount)
		FROM	@sample x
		WHERE	x.Customer	= s.Customer
	) st
	CROSS APPLY
	(
		SELECT	Total = SUM(Amount)
		FROM	@sample x
	) t

SELECT v.Customer,v.Month,v.Year,v.OAmount,v.TarAmount,
--SubTotalOAmount
CASE WHEN v.Customer LIKE '%A%'
	  THEN (SELECT SUM(Amount) FROM TABLE_V WHERE Month=v.Month AND Year=v.Year AND Customer LIKE '%A%')
	 WHEN v.Customer LIKE '%B%'
	  THEN (SELECT SUM(OAmount) FROM TABLE_V WHERE Month=v.Month AND Year=v.Year AND Customer LIKE '%B%')
ELSE OAmount END AS SubTotalOAmount,
--SubTotalTarAmount
CASE WHEN v.Customer LIKE '%A%'
	  THEN (SELECT SUM(TarAmount) FROM TABLE_V WHERE Month=v.Month AND Year=v.Year AND Customer LIKE '%A%')
	 WHEN v.Customer LIKE '%B%'
	  THEN (SELECT SUM(TarAmount) FROM TABLE_V WHERE Month=v.Month AND Year=v.Year AND Customer LIKE '%B%')
ELSE TarAmount END AS SubTotalTarAmount
FROM TABLE_V v
GROUP BY v.Customer,v.Month,v.Year,v.OAmount,v.TarAmount