Sum case when

I have these two separate results in a query but I’d like to bring them together to give one result

, SUM(CASE WHEN d.dec = 4 AND DATEDIFF(d.date2, d.date1) IS NOT NULL THEN DATEDIFF(d.date2, d.date1) ELSE 0 END) AS tD
    , SUM(CASE WHEN d.dec = 4 AND DATEDIFF(d.date2, d.date1) IS NULL THEN DATEDIFF(CURDATE(), d.date1) ELSE 0 END) AS oD

I’ve tried so many things, which were usually met with syntax error but now feel that I’m moving in ever decereasing circles and can’t see the wood for the trees… If anyone could point me in the right direction it would be appreciated

can you describe what you mean by “bring them together” please

@r937 - to the rescue again r937… I want a single result returned rather than two separate columns, effectively putting (adding) tD and oD together to return a single result but I dig myself into a bit of a syntax hole. Originally I did have a query that returned a single result but that result always turned out to be 0 which wasn’t what I was looking for. This is part of a bigger stats query I’m working on and everything, except this bit, appears to be working fine…
Thanking you in advance for your help … yet again

just add them together? easy peasy

[code], SUM(CASE WHEN d.dec = 4 AND DATEDIFF(d.date2, d.date1) IS NOT NULL THEN DATEDIFF(d.date2, d.date1) ELSE 0 END)

  • SUM(CASE WHEN d.dec = 4 AND DATEDIFF(d.date2, d.date1) IS NULL THEN DATEDIFF(CURDATE(), d.date1) ELSE 0 END) AS tD_plus_oD[/code]

There are days that I feel a little dimmer than a 10 what bulb and this is one of them… the word Doh! springs to mind…

well, here you go then… https://www.youtube.com/watch?v=dO37Ql91qqM

you could also do this –

, SUM(CASE WHEN d.dec = 4 THEN COALESCE(DATEDIFF(d.date2,d.date1) ,DATEDIFF(CURDATE(),d.date1)) END) AS tD_plus_oD

That solution looks so much more elegant. Didn’t realise you could use coalasce like that… nice

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.