Help with SQL Grouping

Hi..
I have the following table/data   I need to get the earliest sales date  by product.

PRODUCT        SALES           DATE
APPLES             20                 1/1/2010
BANANAS         5                  3/5/2009
APPLES            9                   2/9/2008
BANANAS            5                     11/29/2007
APPLES             4                   3/6/2007

THE RESULT SHOULD LOOK LIKE

APPLES     33   3/6/2007
BANANAS    10    11/29/2007

LVL 1
JElsterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
SELECT Product, SUM(Sales) AS Sales, MIN([Date]) AS [Date]
FROM SomeTable
GROUP BY Product
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:

SELECT * FROM products p WHERE date = (SELECT MIN(date) FROM products p1 WHERE p1.Product = p.Product )
0
JElsterAuthor Commented:
what's the dff between these two?  thanks
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Patrick MatthewsCommented:
Try them both, and I think the difference will be readily apparent :)
0
dshrivallabhCommented:
Try this

DECLARE @Result TABLE
(
      Product varchar(20),
      Sales int,
      Date Datetime
)

INSERT @REsult VALUES('APPLES',20,'1/1/2010')
INSERT @REsult VALUES('BANANAS',5,'3/5/2009')
INSERT @REsult VALUES('APPLES',5,'11/29/2007')
INSERT @REsult VALUES('BANANAS',20,'1/1/2010')
INSERT @REsult VALUES('APPLES',4,'3/6/2007')
         
Select Product, Sales,Date from @Result
Where Date IN (Select MIN(Date) FROM @Result GROUP BY Product)
0
Patrick MatthewsCommented:
dshrivallabh,

1) Your suggestion will not return the right answer.  Indeed, if the second-lowest date for Product B is the same as the lowest for Product A, then your query will return at least two rows for Product B

2) Please explain how your suggestion is materially different from http:#a30057641, posted more than 8 hours earlier than yours

Patrick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.