urgent - view query - 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

General SQL Server Forums

New to SQL Server Programming

urgent - view query

Author

Topic

mironline
Starting Member

4 Posts

Posted - 2007-05-29 : 07:25:14

Dear Friends
I have 3 form with these names :

Clients <----- Projects <------- Files

I am using this query in my [ web ] page :

select clients.id ,
(select count(*) from projects where projects.clients_id = clients.id) as TotalProjects

from clients

I want to compute SUM for all relevant files for each client's projects.
how i can add it to select select statement ??

thank you.
m.o

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 07:29:46

please post some sample data and required output

mironline
Starting Member

4 Posts

Posted - 2007-05-29 : 07:43:24

Dear Friend.
Thank you for your quick response.it's appreciated
my table's data is listed here :

clients Table

id -------- name
1 -------- Client1
2 -------- Client2

Projects Table
id ------client -- name
1 -------- 1 -------- p1
2 -------- 1 -------- piu
3 -------- 1 -------- p09
4 -------- 2 -------- p_

Files Table
id ---- Projects -------- file
1 -------- 1 -------- MyFiles1
2 -------- 2 -------- MyFiles2
3 -------- 2 -------- MyFiles3
4 -------- 2 -------- MyFiles4
5 -------- 4 -------- MyFiles5

result :
Clients.ID -------- ---total projects -------- -------- Total Files
1 -------- -------- -------- 3 -------- -------- -------- 4( how to find this ?)
2 -------- -------- -------- 1 -------- -------- -------- 1

thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 07:52:16

[code]Select
c.ID,
count(p.name) as [Total Projects],
count(f.File) as [Total Files]
from
Clients c join projects p on c.id = p.Client
Join Files f on p.id = f.Projects
group by c.ID
order by c.ID[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 08:20:59

declare @clients Table (id int, name varchar(50))

insert @clients
select 1,'Client1' union
select 2,'Client2'

declare @Projects Table(pid int, cid int, pname varchar(30))
insert @projects
select 1,1,'p1' union
select 2,1,'piu' union
select 3,1,'p09' union
select 4,2,'p_'

declare @Files Table(fid int, pid int, filen varchar(30))
insert @files
select 1,1,'MyFiles1' union
select 2,2,'MyFiles2' union
select 3,2,'MyFiles3' union
select 4,2,'MyFiles4' union
select 5 ,4,'MyFiles5'

Select Client = min(name), count(pid) as [Total Projects], sum(files) as [Files Count]
From
(Select c.id, c.name,p.pid, 'Files' = (Select Count(*) from @files where pid = p.pid)
from @clients c join @projects p
on c.id = p.cid ) as b
group by id
order by id

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 00:19:53

[code]
select c.id, count(distinct p.pid), count(distinct f.fid)
from clients c inner join projects p
on c.id = p.cid
left join files f
on p.pid = f.pid
group by c.id
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources