CODEWITHSUNDEEP
sqldatabase
user10112169
user10112169

Reputation: 111

sql query to count number of -1's and 1's present in a column

In a table named "table1" contains only 1 column named "column1" contains only -1 and 1. how to write a query to get count of -1 and 1 in the column1 by giving the alias names using single query.

I should print like,

    positive 10
    negative 5

I am just able to print the count of number by writing the query like

select count(*) from table1 group by column1;

but how to give the alias name to both numbers as positive and negative.

Upvotes: 1

Views: 917

Answers (4)

hamid-davodi
hamid-davodi

Reputation: 1974

you could use "sub-queries" like this:

SELECT (select count(*) from table1 group by column1 having column1>0) POAITIVE,
(select count(*) from table1 group by column1 having column1<0) NEGATIVE;

Upvotes: 1

JP_
JP_

Reputation: 1

SELECT 
'Positive' AS [Pos/Neg], 
SUM(CASE WHEN [column1] = 1 THEN 1 ELSE 0 END) AS [Count] 
FROM table1
UNION
SELECT 
'Negative' AS [Pos/Neg], 
SUM(CASE WHEN [column1] = -1 THEN 1 ELSE 0 END) AS [Count]
FROM table1

Upvotes: 0

forpas
forpas

Reputation: 164154

You can GROUP BY column1 and use a CASE expression that returns 'positive' or 'negative':

SELECT CASE column1 
         WHEN 1 THEN 'positive'
         WHEN -1 THEN 'negative' 
       END sign, 
       COUNT(*) counter
FROM table1
GROUP BY column1

But if there is a case that there are no positives or no negatives it is better to use conditional aggregation with UNION ALL:

SELECT 'positive' sign, COUNT(CASE WHEN column1 = 1 THEN 1 END) counter FROM table1
UNION ALL
SELECT 'negative', COUNT(CASE WHEN column1 = -1 THEN 1 END) FROM table1

See a simplified demo.

Upvotes: 1

Hiren Jasani
Hiren Jasani

Reputation: 258

Please try with the below Query and it should be work as you expected.

SELECT
  CASE
    WHEN COLUMN1 = -1 THEN 'NEGATIVE'
    ELSE 'POSITIVE'
  END AS RESULT,
  COUNT(1) AS COUNT
FROM
  TABLE1
GROUP BY
  CASE
    WHEN COLUMN1 = -1 THEN 'NEGATIVE'
    ELSE 'POSITIVE'
  END

Upvotes: 0

Related Questions