Multiple sums from 1 table 1

[FatalExceptionError]

I have a query that seems rather simple but is giving me trouble.

Table A contains orders details including but not limited to
order date
vendor #
order cost

What I am trying to do is get a list with the sum of the order cost for each specific vendor within a givin date. The output is either 1 sum or an error for not using an aggregate. Is it possible to do this in one query or should I just go ahead and make a simple function and call it for each distinct vendor number.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[CaptainD]

It helps if you post your code (or similar code) so people here can see what's missing / wrong etc with the statement

 

[gmmastros]

Does this return what you want?

Select VendorNumber,
       Sum(OrderCost)
From   Table
Where  OrderDate >= '2006-01-01' 
       And OrderDate < '2006-02-01'
[!]Group By VendorNumber[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[FatalExceptionError]

Thank you george it does.

I must need to re-read my understanding of group by as I was getting this error with out it.

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

My understanding of group by was that it was for organizing results. I did not know it impacted queries in such a way

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[gmmastros]

Group By is definetely important.

What if you wanted the sum of [order costs] grouped by the 'state' the order came from. Or, maybe you want them grouped by the zip code where the order came from. How is SQL Server supposed to know? You 'tell' SQL Server how to group the results with the GROUP BY clause.

Please do not think that SQL Server will SORT the data based on the group by. In many cases it will appear that way, but it is NOT reliable. If you want to organize your results (order the data), then you need to use Order By.

In the example I provided earlier, if I wanted the data ordered, I would have written it like this...

Select VendorNumber,
       Sum(OrderCost)
From   Table
Where  OrderDate >= '2006-01-01' 
       And OrderDate < '2006-02-01'
Group By VendorNumber
[!]Order By VendorNumber[/!]

Only with the Order By clause would I be confident that the data was being ordered properly.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[FatalExceptionError]

sweet
thanks for the info

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[SQLSister]

Just so you understand the error for the next time - any timeyou use an agregated column in a select (things like sum, max, min, count) then you must put every nonagregated column inthe selct list into the group by clause otherwise it doesn't know how to do the calculation. The group by determines which records are summed, etc. This can get you into real trouble if you want the some by say customer number but you want to have other data in the select which may have multiple records (even though you only need one of them). To fix this you either end up using min or max to pick one (if you don;t care whic one or using a where clasue to specify which one or using a derived tabble to select just the records you want and than joining to the multiple data (used if you want to calculate the aggregate on one field but want the multiple records to show, for instance.) These can end up being very complex queries to get the data exactly as you need it.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief