Add 2 values from 1 table

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Select sum(case when Loadflag='update' then 1 else 0 end) as 'Records Updated',

sum(case when Loadflag is null then 1 else 0 end) as 'New Records',

sum(case when Loadflag='update' or Loadflag is null then 1 else 0 end) as 'Total'

from xxxx Where loadedDate > '2013-04-30 11:00:00.220'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 2

perfect thanks Mark.

Now just need to work out how it is doing it....

If you could offer a quick overview of how it is doing it I would be very grateful ?

cheers

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 3

Run the same query without the aggregate (i.e. without the SUM) as below, you'll see that each row has either a zero or a one

calculated for it. All we have to do is add up the zero/ones which can be done with the SUM aggregate.

Select case when Loadflag='update' then 1 else 0 end as 'Records Updated',

case when Loadflag is null then 1 else 0 end as 'New Records',

case when Loadflag='update' or Loadflag is null then 1 else 0 end as 'Total', *

from xxxx Where loadedDate > '2013-04-30 11:00:00.220'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 4

Seen , thanks very much for explanation.

Cheers:-)