Lynn Emery

Lynn Emery

  • NA
  • 47
  • 56.7k

selecting and Grouping data

Mar 4 2013 7:02 AM
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.









Answers (1)