Sum Aggregate

• Jim Mraz

  Valued Member

  Points: 63

  More actions

  March 10, 2004 at 1:03 pm

  #110648

  Hi All,

    I have the following scenario. I need to create sums for a field in a view grouped on a per month basis. I tried grouping on our Date field but gives back every single record for that month and not just one total for month. So what is the correct way to do this?

  Also how can I take one value in one record and subtract it from another value in different record?

  JJ

• mimorr

  SSCarpal Tunnel

  Points: 4850

  More actions

  March 10, 2004 at 2:09 pm

  #498181

  JJ, you need to group on datepart(mm,datefield) but this will only work if you have only one calendar year's data.  You could try grouping on datepart(yy,datefield), datepart(mm,datefield)

  As for the second question, you will need to do a self-join back to the table and find some common field(s) to join these two records together.  Might take a look at BOL (Books OnLine) for Using Self-Joins.

  
  
  Michelle

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  March 10, 2004 at 3:36 pm

  #498188

  GROUP BY CONVERT(char(6),DateField,112)

  
  
  --Jonathan

• cliffb

  SSCarpal Tunnel

  Points: 4598

  More actions

  March 11, 2004 at 8:24 am

  #498309

  SELECT YearNumber,

  MonthNumber,

  SUM(Occurances) AS CountOfData

  FROM

  (select YEAR(DateField) AS YearNumber,

  MONTH(DateField) AS MonthNumber,

  Count(datacount) As Occurances

  FROM

  datatable

  GROUP BY SQLDate)Dates

  GROUP BY YearNumber,

  MonthNumber

  Order By YearNumber,

  MonthNumber

  This should give you what you are looking for.