Problem with SQL stmt using SUM

[JohnBates]

Hi ASP'ers,

I'm reading a SQL Server 7 table in my ASP...

sql = "SELECT Carrier, SUM(ItemCount) as totUnits FROM LoadHist WHERE year(DateClosed) = 2002 AND month(DateClosed) = 5 GROUP BY Carrier"

_________________________________________________________

I don't need to group. If I remove the GROUP BY clause.. get this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Column 'LoadHist.Carrier' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.



I just need to sum-up the ItemCount for the entire recordset.

Thanks, John

 

[forecasting]

sql = "SELECT SUM(ItemCount) as totUnits FROM LoadHist WHERE year(DateClosed) = 2002 AND month(DateClosed) = 5"

 

[JohnBates]

Thanks forecasting,

Using your suggestion sql = "SELECT SUM(ItemCount) as totUnits FROM LoadHist WHERE year(DateClosed) = 2002 AND month(DateClosed) = 5"

I no longer get the error, but the totUnits value is zero.

There are 600 rows with various quantities in ItemCount so we should have a total of approx 10,000

John

 

[onpnt]

is there any characters besides numeric in the records? Just checking because the SUM will give you 0 if there are. I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.

 

[JohnBates]

Thanks onpnt,

Well... the data type for ItemCount is 'small integer' and I browsed the recs... did'nt see any that do not look to be numberic. So I would have to say No, there aren't any non-numeric values in that col.

Thanks for the idea though. John

 

[harmmeijer]

What does this get you?
"SELECT COUNT(ItemCount) as totUnits FROM LoadHist WHERE year(DateClosed) = 2002 AND month(DateClosed) = 5"

 

[forecasting]

if there are any nulls in itemcount you may need

sql = "SELECT SUM(ISNULL(ItemCount,0)) as totUnits FROM LoadHist WHERE year(DateClosed) = 2002 AND month(DateClosed) = 5"