Help with sql query using case and group

Question

I have a DB Table with a date and a field called posneg, the value is either 'pos', 'neu' or 'neg'.

I need a result set where the posneg values are added and then grouped by date. Like this

2010-10-03    5   (on that date it could be 12 pos, 5 neu and 7 neg)
2010-10-04    -3  (on that date maybe 10 pos, 2 neu and 13 neg)
...and so on

Basically the neu doesn't matter, as you see.

Andriy M · Accepted Answer · 2011-04-22 07:19:54Z

3

SELECT
  date,
  posneg = SUM(
    CASE posneg
      WHEN 'pos' THEN 1
      WHEN 'neg' THEN -1
      ELSE 0
    END
  )
FROM atable
GROUP BY date

answered Apr 22, 2011 at 7:19

Andriy M

78.1k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jesper Over a year ago

Andriy M's answer worked well. Since it was the first one, I tried that first... I rewrote it to: SELECT dateadd(d, 0, datediff(d, 0, date)) as d, posneg = SUM(CASE posneg WHEN 'pos' THEN 1 WHEN 'neg' THEN -1 ELSE 0 END) FROM Table GROUP BY dateadd(d, 0, datediff(d, 0, date)) order by d So thanks a lot :)

Marco · Accepted Answer · 2011-04-22 06:59:17Z

0

Try this:

SELECT date, SUM(IF(posneg='pos',1,IF(posneg='neg',-1,0))) as total
FROM table GROUP BY date

answered Apr 22, 2011 at 6:59

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Comments

josephj1989 · Accepted Answer · 2011-04-22 07:29:24Z

0

with tab as(

select cast('12/12/2010' as smalldatetime) as dt, 'neg' as posneg 
union all
select cast('12/12/2010' as smalldatetime) as dt, 'pos' as posneg 
union all
select cast('12/12/2010' as smalldatetime) as dt, 'pos' as posneg 
union all
select cast('12/12/2010' as smalldatetime) as dt, 'pos' as posneg 
union all
select cast('12/15/2010' as smalldatetime) as dt, 'neg' as posneg 
union all
select cast('12/15/2010' as smalldatetime) as dt, 'neg' as posneg 
union all
select cast('12/15/2010' as smalldatetime) as dt, 'pos' as posneg
)

select dt,
sum(case when posneg='pos' then 1
         when posneg='neg' then -1
         else 0 end) as total
         from tab
         group by dt

answered Apr 22, 2011 at 7:29

josephj1989

9,7299 gold badges50 silver badges70 bronze badges

Collectives™ on Stack Overflow

Help with sql query using case and group

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related