USING SUB QUERY OR CTE

HI ALL 

I AM HAVING THE REQUIREMENT LIKE CUSTOMER VISITING THE STORE SO MANY TIMES ,

THE MANAGER WANT TO KNOW THE EACH CUSTOMER HOW MANY TIMES VISITED THE STORE AND HOW MUCH AMOUNT HE SPENT ON THE GOODS 

WE CAN GET THE COUNT OF EACH CUSTOMER USING AGGREGATE FUNCTION BUT WE NEED TO SHOW THE COUNT OF EACH CUSTOMER BESIDE THE AMOUNT COLUMN.

FOR THIS I USED SUBQUERY OR CTE TO GET THIS REQUIREMENT

FIND THE BELOW SCRIPT FOR GETTING THIS RESULT

DECLARE @SUBQUERY TABLE(CUSTOMERID INT,AMOUNT INT)

INSERT INTO @SUBQUERY VALUES(1,500)

INSERT INTO @SUBQUERY VALUES(1,600)

INSERT INTO @SUBQUERY VALUES(1,700)

INSERT INTO @SUBQUERY VALUES(1,800)

INSERT INTO @SUBQUERY VALUES(2,500)

INSERT INTO @SUBQUERY VALUES(2,600)

INSERT INTO @SUBQUERY VALUES(3,700)

INSERT INTO @SUBQUERY VALUES(3,800)

INSERT INTO @SUBQUERY VALUES(4,700)

INSERT INTO @SUBQUERY VALUES(4,900)

INSERT INTO @SUBQUERY VALUES(4,200)

INSERT INTO @SUBQUERY VALUES(4,100)

INSERT INTO @SUBQUERY VALUES(4,300)

INSERT INTO @SUBQUERY VALUES(4,400)

--AGGREGATE QUERY:

SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID

--THIS IS USING SUBQUERY:

SELECT MA.*,SUB.NUMBEROFTIMESVISITED FROM (SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID)SUB

JOIN @SUBQUERY MA ON SUB.CUSTOMERID=MA.CUSTOMERID 

--THIS IS USING CTE:

;WITH CTE AS (SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID)

SELECT MA.*,CTE.NUMBEROFTIMESVISITED 

FROM CTE

JOIN @SUBQUERY MA ON CTE.CUSTOMERID=MA.CUSTOMERID 

THANKS FOR VISITING BLOG