Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    11

    Unanswered: sql server count of

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2010
    Posts
    11

    not working

    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

  4. #4
    Join Date
    May 2010
    Posts
    11

    not working

    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •