SQL not calculating/grouping data correctly.

Question

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.

Jignesh Trivedi · Answer

hi so i think in this case you can go with only customer group by and all other condition in where clause because it might possible data in your databse.. Customer A 10000 2010 03 Customer A 50200 2011 03 this also produce multiple result set of same customer.. SELECT Customer SUM(sales) AS [Sales Qty FROM dbo.CustomerSales where Month GROUP BY Customer hope this will help you.

Lynn Emery · Answer

Again than you for your help. Its failing to group all customers together Im still getting multiple occurances of each customer. Rather than using WHERE month Therefore if I select march 2010 it will show me march and all previous months of 2010 in the grid view however it is not adding them together.

Jignesh Trivedi · Answer

hi yes if you remove month from view you can not filter on view. in this case table value function or stored procedure may help you. else you can write direct query (with out use of view) to fill data set. SELECT Customer SUM(sales) AS [Sales Qty] Year FROM dbo.CustomerSales where Month GROUP BY Year Customer hope this will help you.

Lynn Emery · Answer

Hi thank you for your reply. I tried removing the month from the view however this doesn't do exactly want .If the month is not incorporated into the statement then the month dropdown list fails to operate as nothing is controlling it. For example if I select the year 2010 and month march from the dropdown list the sql statement sums the total of sales for the whole of that year rather than the total sales up until march of 2010.

Jignesh Trivedi · Answer

hi in this out put result you are not use month any where so remove month from your view query. SELECT Customer SUM(sales) AS [Sales Qty] Year FROM dbo.CustomerSales GROUP BY Year Customer this is normal behaviour of group by clause. what ever column your passing on group by clause SQL find disctinct value for this column. in your case month id is differant for all record. hope this will help you.