asked on

Count products sold for each of the last twelve months by customer in SQL2005

I need to create a select statement that will produce one row for each customer that counts the products from each of the last twelve months.

The row for each customer would look something like the following.

Customer name
March-2011 (Total products sold)
February-2011 (Total products sold)
January-2011 (Total products sold)
December-2010 (Total products sold)
November-2010 (Total products sold)
October-2010 (Total products sold)
September-2010 (Total products sold)
August-2010 (Total products sold)
July-2010 (Total products sold)
June-2010 (Total products sold)
May-2010 (Total products sold)
April-2010 (Total products sold)

How would I create a select statment to create an export in the avbove format?

Rikin Shah

SELECT CUSTOMER_NAME, iMONTH, COUNT(PRODUCT) FROM TABLENAME
WHERE CUSTOMER_NAME='SearchCriteria'
GROUP BY CUSTOMER_NAME, iMONTH

Open in new window

jdr0606

ASKER

Except I want each row to look like
CustomerName, January, February, March etc.

SOLUTION

Rikin Shah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bhavesh Shah

Hi,

check out this

- Bhavesh

SELECT CustomerName,
		DATENAME(Month,SalesDate)+'-'+Cast(Year(SalesDate)as Varchar(4)) + '('+Cast(Count(Product)as varchar(5))+')'as ProductSale
From TableName
Group by CustomerName, SalesDate

Open in new window

SOLUTION

Bhavesh Shah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial