SUM of values by Month?
-
Hello everyone,
I'm trying to produce data to power a graphing component for an application and i'm having trouble returning what i'm looking for...
What I want is to return two columns:
1-SUM(Usage) for the last x months (the x is unimportant at this point, I really just need to group the Usage Totals by Month)
2-The MONTH associated with the aforementioned Usage Total.
#2 is where i'm having the most difficulty at this point. I can return the usage totals grouped by month, and I really want to show '8/2007' as a label for my x-axis on the graph.
Here is what i'm currently getting the accurate usage data with, but falling short on the labels...
SELECT
SUM(Usage) AS TotalUsage,
CAST(MONTH(Date) AS VARCHAR(2)) + '/' + CAST(YEAR(Date) AS VARCHAR(4)) AS CalcDate
FROM
UBAccountHistory
WHERE
(RecordType IN (5) AND
ServiceCode = ?)
GROUP BY
Date
ORDER BY
Date DESC
Is there a better way to group the data so I can return the correct related month along with the total usage?
Thanks in advance!
Mike
-
changing the date to be 8/2007 should be something easily done on the presentation layer. (What are you using BTW?)
For sorting you absolutely have to return an actual date.
that little dateadd will return the first day of the month that the record belongs to. which allows you to group properly, and sort propertly.
SELECT
SUM(Usage) AS TotalUsage,
dateadd(dd,-DAY(Date)+1,Date) AS CalcDate
FROM
UBAccountHistory
WHERE
(RecordType IN (5) AND
ServiceCode = ?)
GROUP BY
dateadd(dd,-DAY(Date)+1,Date)
ORDER BY
dateadd(dd,-DAY(Date)+1,Date) DESC
then on your presentation layer modify the mm/yyyy display.
-
SELECT
YEAR(Date), MONTH(Date),
SUM(Usage) AS TotalUsage
FROM
UBAccountHistory
WHERE
RecordType IN (5) AND ServiceCode = ? AND Date >= DATEADD(MONTH, -@X, CURRENT_TIMESTAMP)
GROUP BY
YEAR(Date), MONTH(Date)
ORDER BY
YEAR(Date), MONTH(Date)
N 56°04'39.16"
E 12°55'05.25" -
Hey Ray, thanks! That is exactly what I was looking for. I had to change the type of chart I am using to accomodate formatting of the date output at presentation layer, but I can live with a boring 2D bar chart (I guess
)edit: Ray, i'm using Logi Info for my presentation layer. Kinda buggy TBH, but we've been able to do some pretty cool stuff to add value to our products at minimal cost to us.
Peter, thanks for the response, but for some reason your solution did not produce any records against my database.
thanks again for your help guys, it is most appreciated, and i'm on to the next task!
cheers,
Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply