Lynn Emery

Lynn Emery

  • NA
  • 47
  • 56.5k

SQL not calculating/grouping data correctly.

Feb 19 2013 9:25 AM

Hi I have connected three dropdown lists to a grid view via an SQL data source on an aspx web page.
The data source fetches data from a database view which stores customer sales information including customer name, sales, month and year.

My aim is to display the year to date sales for all customers in the grid view.
To do this the user selects the month and year from the dropdown lists. for example When the user selects year 2010 and month March from the drop down list, all sales figures for each customer in 2013 up until march  should appear.

To some degree my SQL code works however I get duplications of customers in my grid view.

Customer     sales  Year   Month
Customer A 10000  2010   03
Customer B 5020    2010   01
Customer B 96854  2010   03
Customer C 25895  2010   01
Customer C 10858  2010   03
Customer C 6987    2010   02

Here is the code I am using which connects the grid view and dropdown lists together

SELECT * FROM [CustomerSales] WHERE (([Year] = @Year) AND ([Month] <= @Month))


However I only want each customer to show up once in the table, therefore it should sum the total sales for each customer for the selected month and all months previous to that within the selected year. (See table below)

Customer     sales     Year
Customer A  10000   2010
Customer B  101874 2010
Customer C  43740   2010


Here is my code for my view which the SQL data source retrieves its data from

SELECT        Customer, SUM(sales) AS [Sales Qty], Year, Month
FROM            dbo.CustomerSales
GROUP BY Year, Customer, Month

I'm not sure if the problem lies within the view design or the SQL data source statement.

I welcome your suggestions.
Thank you in advance.



Answers (5)