CODEWITHSUNDEEP
sqlsql-servert-sql
Charles Morrison
Charles Morrison

Reputation: 428

Result showing two rows to one row

I wrote a query to count positive and negative results per day. My problem is that I am getting two rows per postivie to negative day.

enter image description here

SELECT case([MsrSplatPositive]) when '0' then count([MsrSplatPositive]) end as 'Negative'
,case([MsrSplatPositive]) when '1' then count([MsrSplatPositive]) end as 'Positive'
,CONVERT(VARCHAR(10), f.NewsDate, 106) as 'Date'
 FROM [News].[dbo].[Score] s
inner join [News].[dbo].[Feed] f
on f.ScoreId = s.Id
where f.NewsDate between dateadd(day,-30,GETDATE()) and GETDATE()
group by [MsrSplatPositive],CONVERT(VARCHAR(10), f.NewsDate, 106)

Upvotes: 1

Views: 63

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9129

Remove MsrSplatPositive from the GROUP BY.

That is forcing the positive and negative results into different days.

And then use a SUM(CASE...) instead of CASE(COUNT...)

You have this in the SELECT:

case([MsrSplatPositive]) when '0' then count([MsrSplatPositive]) end as 'Negative'

and instead want this:

sum(CASE WHEN MsrSplatPositive = 0 THEN 1 ELSE 0 END) as 'Negative'

EDIT:

All fixed up should be something like:

SELECT SUM(CASE WHEN MsrSplatPositive = 0 THEN 1 ELSE 0 END) 'Negative'
      ,SUM(MsrSplatPositive 'Positive'
      ,CONVERT(VARCHAR(10), f.NewsDate, 106) as 'Date'
  FROM News.dbo.Score s
       INNER JOIN 
       News.dbo.Feed f on f.ScoreId = s.Id
 WHERE f.NewsDate BETWEEN DATEADD(DAY,-30,GETDATE()) and GETDATE()
 GROUP BY f.NewsDate

Upvotes: 4

Swagata
Swagata

Reputation: 622

Try this...I haven't run the query but think this should achieve the result. I have modified your original query to return 0 instead of null in the positive and negative column. Then used the modified SQL as a temp table and grouped by the date values.

Select Sum(Temp.Negative), Sum(Temp.Positive), Temp.Date From
(
SELECT case([MsrSplatPositive]) when '0' then count([MsrSplatPositive]) 
    else 0 end as 'Negative'
,case([MsrSplatPositive]) when '1' then count([MsrSplatPositive] 
    else 0) end as 'Positive'
,CONVERT(VARCHAR(10), f.NewsDate, 106) as 'Date'
FROM [News].[dbo].[Score] s
inner join [News].[dbo].[Feed] f
on f.ScoreId = s.Id
where f.NewsDate between dateadd(day,-30,GETDATE()) and GETDATE()
group by [MsrSplatPositive],CONVERT(VARCHAR(10), f.NewsDate, 106)
) Temp
Group By Temp.Date

Hope this helps.

Upvotes: 0

Related Questions