CODEWITHSUNDEEP
sqlmysqlgroup-by
Jules
Jules

Reputation: 7774

MySql, how can I group by, in a select query on positve or negative?

I would like to group by all the the values which are negative and all those which are positive, any ideas how to do this ?

Upvotes: 9

Views: 5973

Answers (5)

Santhosh N
Santhosh N

Reputation: 82

I know the question was asked long ago, but it may help someone in the future. NOTE: Below was tested in SQL server, I believe the same logic will apply in MySql.

    declare @temp table(
    id int,
    amount decimal(18,2),
    rate decimal(18,2),
    isPositiveAmount bit,
    isPositiveRate bit
)

insert into @temp values(1, 100.1, 12.3, null, null)
insert into @temp values(1, -1, -1, null, null)
insert into @temp values(2, 100.1, 12.32, null, null)

update @temp set isPositiveAmount = iif(amount >= 0, 1, 0), isPositiveRate = iif(rate >= 0, 1, 0)

select id, sum(amount), avg(rate), isPositiveAmount, isPositiveRate from @temp
group by id, isPositiveAmount, isPositiveRate

Upvotes: 0

Thilo
Thilo

Reputation: 17735

Try something like this:

select count(*), IF(foo >= 0, "pos", "neg") as sign  from test group by sign;

where foo is the column with the positive or negative values

EDIT: Stefan's solution is more elegant and better if you want zero values treated differently from positive and negative ones.

Upvotes: 1

TehShrike
TehShrike

Reputation: 10084

Stefan's solution looks like the best answer so far. If you would prefer for 0 to be grouped in with positive numbers, you can use

GROUP BY `field` >= 0

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

SELECT SUM(CASE WHEN SomeColumn < 0 THEN 1 ELSE 0 END) AS negative_values,
       SUM(CASE WHEN SomeColumn >=0 THEN 1 ELSE 0 END) AS non_negative_values
    FROM YourTable

Upvotes: 7

Stefan H Singer
Stefan H Singer

Reputation: 5504

GROUP BY SIGN(field) should work.

Upvotes: 16

Related Questions