distinct of a sum - 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

distinct of a sum

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-12-04 : 15:55:22

I need help with the second query below, that will produce a count of distinct records.

the first query produces a unique set of records of project id's that have at least one constraint.

the second query produces just a single number that counts the number of projects that have a constraint.

I want the second query to count just the 'distinct' list of project ID's..currently it is including projects that have more than one constraint, because I dont understand how to filter out the projects that have more than one constraint....wich is what 'distinct' does for me in the first query...make sense?

SELECT distinct constraints.project_ID
from project inner join constraints on constraints.project_ID=project.project_id
where ProjectState='active' and (tier='tier 2' or tier='tier 3')

SELECT [TotalConstrained]= SUM(CASE WHEN (Projectstate='active'and (tier='tier 2' or tier='tier 3')) THEN 1 ELSE 0 END) from project inner join constraints on constraints.project_ID=project.project_id

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-04 : 16:19:20

since your 2nd query is just counting (sum 1 when match 0 when not match)


select count(distinct  constraints.project_ID) as [TotalConstrained]
from project inner join constraints on constraints.project_ID=project.project_id
where ProjectState='active' and (tier='tier 2' or tier='tier 3')

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-12-05 : 11:17:24

thank you!..that is exactly what I wanted!

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-12-05 : 16:17:41

*sigh*...now I need to produce a metric from this query...one query produces one total..the second another total..the metric is the first divided by the second...how do I merge this into a query that would allow me to store the metric as a single record...

record# constrained total %constrained datestored
1 21 54 .388888888 12/5/2008

this is what I got so far, but am stuck on how to join two numbers into a dataset..ie. the last line 'on' is needed?

SELECT t1.TotalConstrained, t2.TotalProjects,
t1.TotalConstrained*1.0/t2.TotalProjects AS [ProjectsConstrained],
[DateStored] = getdate()
FROM
(
select count(distinct constraints.project_ID) as [TotalConstrained]
from project inner join constraints on constraints.project_ID=project.project_id
where ProjectState='active' and (tier='tier 2' or tier='tier 3')
)t1
INNER JOIN(
select count(distinct project.project_ID) as [TotalProjects]
from project
where ProjectState='active' and (tier='tier 2' or tier='tier 3')
)t2
on t1.project_id=t2.project_ID

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-05 : 16:50:09

[code]select count(distinct constraints.project_id) as [TotalConstrainted],
count(distinct project.project_id) as [TotalProjects],
count(distinct constraints.project_id) as [TotalConstrainted]*1.0/count(distinct project.project_id) as [TotalProjects]
as [ProjectsConstrainted], getdate() as datestored
from project left outer join constraints on project.project_id = constraints.project_id
where projectstate = 'active' and (tier='tier 2' or tier = 'tier 3')[/code]

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-12-05 : 16:55:04

you have no idea how much this helps!...both in solving my problem..and in analyzing the solution...

thank you!

obtw...very elegant solution!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources