CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Aaron
Aaron

Reputation: 686

SQL Statement for counting how many times a record is changed

I have a unique requirement where I have 3 audit tables where any time a change is made it creates a new record for the change. now I need to create a view that will get the distinct order id's and how many time they are changed in the 3 respected tables.

this is my SQL I have developed 

select  distinct orderid as salesorderid,COUNT (*) AS SALESORDERUPDATE  
FROM  foobar 
where ModifiedDateTime>'2015-11-01 20:44:55.107' group by Orderid
union all 
select  distinct orderid as salesorderid,count(*)  SALESORDERUPDATE  AS   
from foobar1 
where ModifiedDateTime>'2015-11-01 20:44:55.107' group by OrderID
union all
select distinct orderid as salesorderid,count(*)AS SALESORDERUPDATE  
from foobar2 
where ModifiedDateTime>'2015-11-01 20:44:55.107' group by  Orderid;

Now the results I get are like this for orderid 1 i have 3 entries showing different values for count (*). I want to sum that up in one line

so it would be something like 

orderid  123  salesorderupdate(20) 
as suppose to 
orderId 123 salesorderupdate(10)
orderid 123 salesorderupdate(7)
orderid 123 salesorderupdate(3)

Thanks,

Upvotes: 0

Views: 116

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Like this:

 select orderID, sum(salesorderupdate)
 from 
 (the query from your question goes here) temp
 group by orderId

Upvotes: 2

mccalljt
mccalljt

Reputation: 796

Make your query the subtable, then select the id and the sum

select distinct orderid, sum(SALESORDERUPDATE)
from (
select  distinct orderid as salesorderid,COUNT (*) AS SALESORDERUPDATE  
FROM  foobar 
where ModifiedDateTime>'2015-11-01 20:44:55.107' group by Orderid
union all 
select  distinct orderid as salesorderid,count(*)  SALESORDERUPDATE  AS   
from foobar1 
where ModifiedDateTime>'2015-11-01 20:44:55.107' group by OrderID
union all
select distinct orderid as salesorderid,count(*)AS SALESORDERUPDATE  
from foobar2 
where ModifiedDateTime>'2015-11-01 20:44:55.107' group by  Orderid;
) changes group by orderid;

Upvotes: 1

Related Questions