selecting and Grouping data
Hi I am trying to select sepecific data from a table and group it.
In the table which I am extracting the data from I have the following fields:
Table Fields: Customer¦Sales¦ Date¦Description
Example table data:
Customer¦ Sales ¦Date¦ Product description
Customer A¦20¦ 01/10/2010¦ Product description
Customer A¦10¦ 05/10/2010¦ Product description
Customer B¦35¦ 21/09/2010¦ Product description
Customer B¦15¦ 16/09/2010¦ Product description
I want to create an SQL statement which filters and selects data from the table to show the the highest Sales for each customer by month and year.
Therefore the data should display this from the example table data above:
Customer¦ Sales ¦Month¦Year¦ Product description
Customer A¦20¦ 10¦ 2010¦ Product description
Customer B¦35¦ 09¦2010¦ Product description
However I am getting duplications for each month and year rather than a single entry.
for example:
Customer¦ Sales ¦Month¦Year¦ Product description
Customer A¦20¦ 10¦ 2010¦ Product description
Customer A¦10¦ 10¦ 2010¦ Product description
Customer B¦35¦ 09¦2010¦ Product description
My SQL statement appears to work fine until I add the field product description.
Below shows my current SQL statement:
SELECT Customer, Description, MAX(sales) AS Maxsales, CASE WHEN MONTH([Date]) = '01' THEN '01' WHEN MONTH([Date_Rec]) = '02' THEN '02' END AS Month,CASE WHEN YEAR([Date]) = '2010' THEN '2010' END AS Year
FROM dbo.CCAR_Log
GROUP BY Customer, Description, sales CASE WHEN MONTH([Date]) = '01' THEN '01' WHEN MONTH([Date]) = '02' THEN '02' END, CASE WHEN YEAR([Date]) = '2010' THEN '2010' END
I hope that all makes sense.
Thank you for your help in advance.