sql server count of

tmcrouse · #1 (**permalink**) 02-03-12, 16:37

How do I count the number of positive dollar values in a query?
I have various negative and positive dollar values and want to count how many positive I have then I will know how many are negative. I cannot put >0 or >0.00 in the where because it still returns everything cuz the programmer set the datatype to be money

Pat Phelan · #2 (**permalink**) 02-03-12, 16:58

Code:

CREATE TABLE #foo (
   foo          MONEY       NULL
)

INSERT INTO #foo
   SELECT NULL
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT -3
   UNION ALL SELECT -4
   UNION ALL SELECT -5
   UNION ALL SELECT 0
   UNION ALL SELECT 0
   UNION ALL SELECT 0
   UNION ALL SELECT 0

SELECT
   COUNT(CASE WHEN foo IS NULL THEN 1 END) AS [NULL]
,  Count(CASE WHEN 0 < foo THEN 1 END) AS positive
,  Count(CASE WHEN foo < 0 THEN 1 END) AS negative
,  Count(CASE WHEN 0 = foo THEN 1 END) AS zero
   FROM #foo

DROP TABLE #foo

-PatP

tmcrouse · #3 (**permalink**) 02-03-12, 17:10

this is not working. of course i replaced your items with my actual table and column names. The table has 16 million records and values vary from $0.00 to $5,000.00 and $-0.01 to $-5,000.00 and there are 16 million different versions that are between all these numbers

tmcrouse · #4 (**permalink**) 02-03-12, 17:12

and again this is a table that i have read rights only to. the programmer only has the write to do any sort of create table. i can only query.

Pat Phelan · #5 (**permalink**) 02-03-12, 17:49

Does my example code work? You can just cut-and-paste, you don't need any special permissions to run it. We need to get the example to work first, then we can adapt it to your table.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech