CODEWITHSUNDEEP
sqlsql-serversql-server-2005

Reputation:

SQL query to test my programming capabilities in different way

I have one table and one column in it. There is 15 rows (integers). I want to count the positive numbers and negative numbers, and also sum of total numbers in one query.

Can any one help me?

Upvotes: 1

Views: 239

Answers (4)

Adriaan Stander
Adriaan Stander

Reputation: 166606

Try this

SELECT  SUM(CASE WHEN Col > 0 THEN 1 ELSE 0 END) AS Pos,
        SUM(CASE WHEN Col < 0 THEN 1 ELSE 0 END) AS Neg,
        SUM(Col) AS Tot
FROM    Table

Upvotes: 1

Rob Farley
Rob Farley

Reputation: 15849

Or...

SELECT  
     COUNT(CASE WHEN Col > 0 THEN 1 END) AS NumPositives,
     COUNT(CASE WHEN Col < 0 THEN 1 END) AS NumNegatives,
     SUM(Col) AS Tot
FROM  TableName;

Or you could consider using SIGN(Col), which gives 1 for positive numbers and -1 for negative numbers.

Upvotes: 3

Daren Thomas
Daren Thomas

Reputation: 70354

select (select sum(mycolumn) from mytable where mycolumn > 0) as positive_sum,
       (select sum(mycolumn) from mytable where mycolumn < 0) as negative_sum,
       sum(mycolumn) as total_sum
from   mytable

Upvotes: 1

gbn
gbn

Reputation: 432662

I'll give you psudeo code to help you with your homework.

3 aggregates:

  • SUM
  • SUM (CASE < 0)
  • SUM (CASE > 0)

Upvotes: 1

Related Questions