|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
January 8th, 2007, 11:32 AM
|
|||
|
|||
Need help with summing by month
have been trying to convert from a VBA statement to a SQL server statement. Need the following results:
Month Grade Bag Size Lbs. Jan. 1 50 285 Jan. 1 100 150 Feb. 1 50 136 Feb. 2 200 115 I think you get the idea: sort by distinct [Date], [Grade],[Bag Size] and give a sum for the entire month. So far I have the following code: SQLstatement = "SELECT distinct([Date]),([Grade]),([BagSize_lbs]), " & _ " Sum([ShiftTons]) " & _ " FROM dbo.Table" & _ " where ([Date] between '" & startdate & "' and '" & enddate & "') " & _ "GROUP BY [date], [BagSize_lbs],[Grade]" but this does not give it in a by month format and a total for the tons by Bagsize.... i get: 1/1/2006 828 2000 35.38 11/10/2006 813 55 8.98 11/10/2006 813 1000 36.74 11/10/2006 813 1600 18.87 11/10/2006 813 2000 12.70 please help, I am new to SQL server and have been unable to find any examples using distinct for so many columns and also using sums with it. Thanks
|
|
#2
January 8th, 2007, 12:36 PM
|
||||
|
||||
|
since each individual date is distinct, what you're selecting is happening. if you just want to sum by month/year try something like this:
Code:
SQLstatement = "SELECT convert(varchar(2),month([Date]))+convert(varchar(4),year([Date])) as [Month],([Grade]),([BagSize_lbs]), " & _ " Sum([ShiftTons]) " & _ " FROM dbo.Table" & _ " where ([Date] between '" & startdate & "' and '" & enddate & "') " & _ "GROUP BY convert(varchar(2),month([Date]))+convert(varchar(4),year([Date])),[BagSize_lbs],[Grade]" also if you're using group by, there's no need for distinct ... they both do the same thing
__________________
Come JOIN the party!!! Quote of the Month: Retirement: Because you've given so much of yourself to the company that you don't have anything left we can use. Questions to Ponder: What do you do when you see an endangered animal eating an endangered plant? iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm") copyright© 2008 sbenj69
|
|
#3
January 8th, 2007, 01:05 PM
|
|||
|
|||
|
Thank you so much. My boss and I have been working on it for a couple of days off and on. A little work to format the month name and will have it done. Greatly appreciated.
|
|
#4
January 8th, 2007, 01:54 PM
|
|||
|
|||
|
have tried it and it seems to work mostly. am having trouble with the date. it returns 6/29/1905 rather than January 2005 with a start date of 1/1/2005 and a enddate of 2/1/2005. anymore suggestions?
|
|
| Viewing: ASP Free Forums > Other > Programming Help > Need help with summing by month |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
