get count for all items - SQL Server Forums

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)

get count for all items

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-12-26 : 00:35:01

Hi
I have two table like below:

Services( int ServiceID, nchar(30) ServiceName)
Works(int workID,int ServiceID,int UserID,nchar(10)date,nchar(10)time);

i want to get the number of all services we done between two dates.[condition for dates not matter to me by now]

consider this data in tables:

Services( ServiceID, ServiceName)
1, "service1"
2, "service2"
3, "service3"

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

first i used this query :

select serviceID,count(serviceID)from Works group by serviceID

the result is :
1,2
2,1

and ther is no row for serviceID=3 with count =0
so to have a row with count 0 i tried this query:

declare @val varchar(max)
set @val='';
select @val=@val +',[' +cast(serviceID as varchar(5))+']' from Services
select @val=stuff(@val,1,1,'')
declare @sql varchar(max)
set @sql ='
select * from 
	(select serviceID,workID from View_Works)p
pivot
	(count(workID) for serviceID in ('+@val+'
		)) as pvt'
execute (@sql)

and the result by this query is good but when we use pivot, the is one row, but i want it in one row for each ServiceID

please guide me
thanks

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-26 : 03:31:53

Try something like this:


SELECT S.ServiceID, COUNT(W.ServiceID)
  FROM Services AS S
       LEFT OUTER JOIN
       Works AS W
       ON W.ServiceID = S.ServiceID
 GROUP BY S.ServiceID;

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-12-26 : 23:40:32

it work's.
thanks a lot

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-12-26 : 23:52:14

sorry i have problem with adding condition about date to this query.
can u help me again.
thanks

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-12-27 : 00:16:21

i changed query to this. this is working but can we write a better query?


SELECT S.ServiceID, COUNT(W.ServiceID) as cnt
  FROM Services AS S
       LEFT OUTER JOIN
       (SELECT ServiceID FROM Works where [date] BETWEEN '1389/01/01' AND '1390/01/01')AS W
       ON W.ServiceID = S.ServiceID
GROUP BY S.ServiceID;

Sachin.Nand

2937 Posts

Posted - 2010-12-27 : 08:24:15

This?


SELECT S.ServiceID, COUNT(W.ServiceID) as cnt
FROM Services AS S
LEFT OUTER JOIN Works W ON W.ServiceID = S.ServiceID
WHERE [W.date] BETWEEN '1389/01/01' AND '1390/01/01'
GROUP BY S.ServiceID;

PBUH

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-28 : 19:54:29

quote:

Originally posted by Sachin.Nand

This?


SELECT S.ServiceID, COUNT(W.ServiceID) as cnt
FROM Services AS S
LEFT OUTER JOIN Works W ON W.ServiceID = S.ServiceID
WHERE [W.date] BETWEEN '1389/01/01' AND '1390/01/01'
GROUP BY S.ServiceID;

PBUH

Adding the date condition in the WHERE clause will eliminate services that do not have correspondence works in the specified date.
I think the proper way is to add date condition to join conditions like this:


SELECT S.ServiceID, COUNT(W.ServiceID) AS cnt
  FROM Services AS S
       LEFT OUTER JOIN
       Works AS W
       ON W.ServiceID = S.ServiceID
          AND W.date BETWEEN '1389/01/01' AND '1390/01/01'
 GROUP BY S.ServiceID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources