Sum Aggregate

  • 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

  • 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

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



    --Jonathan

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply