[RESOLVED] Getting a row count from a SUM

[phpSimon]

Hi all

Is there a simple way of getting a row count from a query that performs a sum and returning it at the same time as the sum? This is for MS SQL.

I get a total like this:

SELECT Sum(revenue) AS total FROM Sales WHERE CustomerID = '1'

I've tried (based on advice found elsewhere) :

SELECT count (*) AS 'row count' FROM (
     SELECT Sum(revenue) AS total FROM Sales WHERE CustomerID = '1'
     ) 
AS t

(The final alias 't' is apparently required - it doesn't work without, but I don't know why).

When I do this, I only get the result for t, not the Sum as well - I can see why but I don't know how to get both results from the same query.

Also, the value of t seems incorrect; it returns 1 even when the row count is zero. I want to do something with the value 'total' but only if the row count is not zero.

Many thanks

[phpSimon]

I think I guessed at the answer:

SELECT Count (revenue) AS count, Sum(revenue) AS total FROM Sales WHERE CustomerID = '1'

This seems to return the number of rows and the sum at the same time.

Any comments appreciated!

[Res]

That's what we would have typed at you. Good work fingering it out. If you don't mind setting the thread to resolved using the thread tools at the top, this will prevent other guys and gals from coming in to help you when you've helped yourself.

[phpSimon]

Thank you!