May 1, 2013 at 2:53 am
Morning all
Good someone offer the best way to do this.
I want to add 2 values from 1 table.
Example:
Select count (*) as 'Records Updated' from xxxx Where loadedDate > '2013-04-30 10:02:37.220'
and Loadflag='update' ... gives say 700 records
then want to add output from :
Select count (*) as 'New Records' from xxxx Where loadedDate > '2013-04-30 11:00:00.220'
and Loadflag is NULL ...gives say 1000 records
so I want 1 query that would add the values together to give output count as total.
many thanks for any advice
May 1, 2013 at 3:09 am
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
http://www.sqlservercentral.com/articles/Best+Practices/61537May 1, 2013 at 3:15 am
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
May 1, 2013 at 3:26 am
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
http://www.sqlservercentral.com/articles/Best+Practices/61537May 1, 2013 at 3:33 am
Seen , thanks very much for explanation.
Cheers:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply