-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

USE credit
go

-- DROP VIEW dbo.SumOfAllChargesByMember

CREATE VIEW dbo.SumOfAllChargesByMember
	WITH SCHEMABINDING  -- required if you plan to index this view!
AS
SELECT c.member_no AS MemberNo, 
	COUNT_BIG(*) AS NumberOfCharges, -- required when GROUP BY is in an indexed view!
	SUM(c.charge_amt) AS TotalSales
FROM dbo.charge AS c
GROUP BY c.member_no
go

--DROP INDEX dbo.SumOfAllChargesByMember.SumofAllChargesIndex

CREATE UNIQUE CLUSTERED INDEX SumofAllChargesIndex
	ON dbo.SumOfAllChargesByMember (MemberNo) 
go

