Select earliest date and qty total

• Greg Roberts

  Valued Member

  Points: 70

  More actions

  October 31, 2006 at 10:41 pm

  #117351

  I'm having a brain spasm day, and I just can't get my head around this query

  I have a simple table that has 3 columns -

  Number, Date, Qty

  1,31/01/2006,10

  1,03/02/2006,20

  2,06/07/2006,5

  1,31/01/2006,3

  2,02/02/2006,6

   

  I'm trying to write a query that will give the me the earliest date and qty total.

  Result should be -

  1,31/01/2006,13

  2,02/02/2006,6

   

  Greg

• mrivera

  Ten Centuries

  Points: 1000

  More actions

  November 1, 2006 at 12:02 am

  #669140

  select

  top 2 number, date, sum(qty)

  from <table>

  group by number,date

  order by date asc

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  November 1, 2006 at 12:15 am

  #669143

  Greg,

  Here is what i got from your inputs.....

   

  CREATE TABLE YourTable( Number SMALLINT , [Date] SMALLDATETIME , Qty INT )

  GO

   

  INSERT INTO YourTable( Number , [Date] , Qty )

  SELECT 1 , '2006-01-31 00:00:00' , 10

  UNION ALL

  SELECT 1 , '2006-02-03 00:00:00' , 20

  UNION ALL

  SELECT 2 , '2006-07-06 00:00:00' , 5

  UNION ALL

  SELECT 1 , '2006-01-31 00:00:00' , 3

  UNION ALL

  SELECT 2 , '2006-02-02 00:00:00' , 6

  UNION ALL

  SELECT 3 , '2006-02-02 00:00:00' , 15  -- add an extra record

  GO

  SELECT t1.Number, t1.Date , SUM( t1.Qty )

  FROM YourTable t1

   INNER JOIN ( SELECT Number , MIN([Date]) AS [Date] FROM YourTable GROUP BY Number ) t2 ON t1.Number = t2.Number AND t1.Date = t2.Date

  GROUP BY t1.Number, t1.Date

  DROP TABLE YourTable

  --Ramesh
  

  ---

• Greg Roberts

  Valued Member

  Points: 70

  More actions

  November 1, 2006 at 2:45 am

  #669156

  Thanks Ramesh

  The Solution you've given has put me on the right track.

  Although the other solution is correct for the given example it's not generic.

  Greg