Count by distinct value when all parts not null

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

Count by distinct value when all parts not null

Author

Topic

workSQL
Starting Member

6 Posts

Posted - 2007-03-12 : 14:05:10

Hello,

I'm fairly new to SQL as so am looking for help with an ad hoc query. The data in focusing on is from table named APs:

FileNo Type PartNo Completed
6 Northbound 1 03/03/2007
6 Northbound 2 NULL
6 Other 1 NULL
6 Other 2 NULL
20 Proof 0 19/07/2006
20 TCP 0 21/07/2006
24 40-Day 1 16/01/2006
24 40-Day 2 16/03/2006
24 Other 0 NULL
44 Northbound 1 16/01/2006
44 Northbound 2 16/06/2006
44 Northbound 3 16/12/2006
44 Northbound 4 01/01/2007

I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):

SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs
FROM APs
WHERE (completed IS NOT NULL)
GROUP BY [File]

Total count should be: 4

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 14:51:11

You say the total in your example should be 4. I see two rows where PartNo is 0 and Completed is not null, so I would say from your explanation that the total should be 2. Please explain how you get 4, I'm either understanding you wrong, or your data is wrong?

workSQL
Starting Member

6 Posts

Posted - 2007-03-13 : 08:34:55

Hi snSQL,

The total should be 4 because there are two completed with 0 parts, plus two which have all parts completed per type per fileNo:

FileNo 20 has type Proof & type TCP, 0 parts completed = 2
FileNo 24 has type 40-Day, parts 1 & 2 completed = 1
FileNo 44 has type Northbound, parts 1, 2, 3 & 4 completed = 1
Total: 4

Thanks.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-13 : 12:59:08

I don't think this is exactly right because I'm not sure I understand all your rules, but it should be close

create table #Test (
FileNo int,
Type varchar(50),
PartNo int, 
Completed datetime
)
insert #Test
select 6, 'Northbound', 1, '03/03/2007' union all
select 6, 'Northbound', 2, NULL union all
select 6, 'Other', 1, NULL union all
select 6, 'Other', 2, NULL union all
select 20, 'Proof', 0, '7/19/2006' union all
select 20, 'TCP', 0, '7/21/2006' union all
select 24, '40-Day', 1, '1/16/2006' union all
select 24, '40-Day', 2, '3/16/2006' union all
select 24, 'Other', 0, NULL union all
select 44, 'Northbound', 1, '1/16/2006' union all
select 44, 'Northbound', 2, '6/16/2006' union all
select 44, 'Northbound', 3, '12/16/2006' union all
select 44, 'Northbound', 4, '01/01/2007'

select FileNo, Type, count(distinct Type) as [Count]
from #Test
group by FileNo, Type
having count(*) = count(Completed)

go
drop table #Test

workSQL
Starting Member

6 Posts

Posted - 2007-03-13 : 15:28:15

Sorry no, it is still counting parts that aren't complete. Maybe shortening the sample would help:

FileNo Type PartNo Completed
6 Northbound 1 2006-12-01
6 Northbound 2 NULL
6 Other 0 2007-01-01
24 TCP 1 2006-11-05
24 TCP 2 2007-02-05

Query to show completed types for each fileNo should result in a count of 2.
Count 1 for (FileNo:6, Type: Other) + count 1 for (FileNo: 24, Type: TCP).
There is no set range of parts per type, it could be any number of parts, or it may not have parts, in which case the partNo would be zero.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-13 : 16:43:39

When I run the query on your new data I get

FileNo      Type              Count       
----------- ----------------- ----------- 
6           Other             1
24          TCP               1

That looks like exactly what you said, total count of 2.
Count 1 for (FileNo:6, Type: Other) + count 1 for (FileNo: 24, Type: TCP).

workSQL
Starting Member

6 Posts

Posted - 2007-03-14 : 07:42:42

Hi,

Yes, you're right, it does result in the correct count when creating the test table but on my actual database it is counting all of the parts completed or not. I've never created a test table before so I'm not understanding why the test table results in the correct count by using the select statement from the bottom portion of your query doesn't. Would it have something to do with the 'union all'? Sorry, rather new at SQL.
I've made this query below but have yet been unable to figure out how to to count the max(partNo)s for just the completed types. The field names are a little different from my sample but are essentially the same.

select clientID, typeName, max(partNo) AS [partNo], count(distinct typeName) as [Count]
from dbvMonitoringAndActionPlans
group by clientID, typeName
having count(*) = count(Completed)

I appreciate the help.

workSQL
Starting Member

6 Posts

Posted - 2007-03-14 : 10:28:00

The slightly modified query below is giving me the correct count now but I don't think it will work if a type as 3 or 4 parts or more and only the part 1 and part 2 are complete.

select clientID, typeName, max(partNo) AS [partNo], count(distinct typeName) as [Count]
from dbvMonitoringAndActionPlans
where (completed <> '')
group by clientID, typeName
having (max(partNo) = 0) OR (max(partNo) > 1) AND (count(*) = count(Completed))

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-14 : 11:56:58

Unless you can give me sample input data that generates the wrong results you can't expect me to fix anything. I run the query on the data you give me, and it gives the correct result, but you say "ah yes, but with different data, the results are wrong" and then you don't supply that data.

If you can't give me data that breaks the query, I can't help you.

workSQL
Starting Member

6 Posts

Posted - 2007-03-14 : 15:05:56

Just a couple of the field names are different, the data itself is actually the same, there's just hundreds of rows of it. The problem I'm having is that it works when I create the test table using your your SQL and then running the select query you included. When I run the same select query against the actual table in 'SQL Query Analyzer' it is returning counts for each distinct fileNo and type when only part 1 of a 2, 3 or 4 part type has a completed date. I don't know why except perhaps it works because of the way the sample data is saved to the temp table allows the select statement to pull the correct data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources