TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Lynn Emery
NA
47
56.5k
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.
Reply
Answers (
1
)
Pivoting to a Single Column in SQL
Select Where Maximum