sum of count of two columns from two diff tbls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sum of count of two columns from two diff tbls

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-12-27 : 01:05:31

Hi
I have 4 table like below:
Users(int userID, nchar(30) name, nchar(30) userName, nchar(30) pwd)
Works(int workID,int ServiceID,int UserID,nchar(10)date,nchar(10)time);
CoWorkers(int workID,int userID)

i want to get the number of works done by each user between two dates.
with considering this data in table:
Users( userID, name)
1, "name1"
2, "name2"
3, "name3"

Works( workID, userID)
1,1
2,3
3,1

CoWorkers(workID,userID)
1,3
2,2

we should get results like this :
name,cnt
user1,2
user2,1
user3,2

for example user3 has one work in table "works" and one work in table "coWorkers". so count of works he doen is some of those, equal to 2.

i tried this query:

SELECT U.[name], COUNT(W.userID)+(Select count(userID) From CoWorkers as cnt
  FROM Users AS U
       LEFT OUTER JOIN
       (SELECT userID FROM Works where [date] BETWEEN  '1389/01/01' AND '1390/01/01')AS W
       ON W.userID = U.userID
GROUP BY U.userID,U.[name];

but this query returns only count for table Works. how can i add the result of this query with the number of works doen by each user in table CoWorkers?
please help me

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-27 : 02:41:01

Try

Select u.name, isnull(count(w.UserID,0) + isnull(Count(c.UserID),0) as cnt
from Users U
left outer join Works w on U.userid = w.userid
left outer join CoWorker c on u.userid = c.userid
group by u.name

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-27 : 02:43:06

Try

--To include date part
Select u.name, isnull(count(w.UserID,0) + isnull(Count(c.UserID),0) as cnt
from Users U
left outer join Works w on U.userid = w.userid and w.date between '2010-01-01' and '2010-12-25'
left outer join CoWorker c on u.userid = c.userid
group by u.name

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-12-27 : 03:03:32

thanks pk_bohra, that's incredibly working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources