April 14, 2005 at 4:44 pm
ARGH! ALWAYS COPY MESSAGE INTO WORD BEFORE PREVIEWING! Yes, the Preview Monster just ate my post. Sigh. The rewrite should be a bit more concise.
OK. I have a strange count/group by situation and right now I’m stumped. Two tables, Owner, Child. Child contains three fields called OwnerID, ChildID, and Status. Status will have one of three values, YES, NO, MISSING. We could care less about the ChildID field and it shall not be mentioned hereafter. The Owner table consists of OwnerID and NoCount.
Obviously the real tables are hideously more complicated, but this will serve.
Doing a count/group by on the child table, you would see something like this:
Result Set 1:
OwnerID, Status, NoCount
1, YES, 3
1, NO, 2
1, MISSING, 2
2, YES, 1
2, MISSING, 2
3, YES, 4
4, MISSING, 2
5, NO, 6
That part is easy. Now I need to roll this up on OwnerID for Status = NO. This is what I need:
Result Set 2:
OwnerID, NoCount
1, 2
2, 0
3, 0
5, 6
SQL will, of course, just return owners 1 & 5.
Owner 4 is a special case, it has no YES or NO. I cannot assume that there are any NO records if I only have MISSING, I can only assume NOs if I have YES and no NO records. This system's specification says that a value of -1 is effectively a null or no result. OwnerID.NoCount is defaulted to -1 prior to this point, then when I update linked by OwnerID, the records for which I don’t have a value are not touched.
Cute, ain’t it?
I think I could get the result by inserting a NO, 0 into a temp table for every owner ID for which I have a YES in Result Set 1, then insert all RS1 records where Status = NO, then do a SUM(NoCount) GROUP BY OwnerID, but I’d rather avoid a temp table. I would then UPDATE Owner table SET NoCount WHERE OwnerID….
I tried doing it through a CASE statement, but that’s not working out for me right now. I’ll continue experimenting with it and see if I can come up with a solution while I’m waiting for a reply to this post.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 14, 2005 at 5:15 pm
Well, I have a solution but I’m not sure how much I like it.
/*
create view vParentStatus as
select OwnerID, 'NOTNO' as Status, count(*) as 'NoCount'
from Child
group by OwnerID, Status
having Status <> 'NO'
union
select OwnerID, Status, count(*)
from Child
group by OwnerID, Status
having Status = 'NO'
union
select OwnerID, 'NO', 0
from Child
where Status = 'YES'
*/
select OwnerID, Status, sum(NoCount) as NoCount
from vParentStatus
group by OwnerID, Status
having Status = 'NO'
Can anyone suggest any other ways? I don't have a problem using a view, it just feels to me like there should be a less complicated solution.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 14, 2005 at 5:21 pm
How about this one:
create table #tblA(ownerid int, status char(7),nocnt int)
insert #tblA(ownerid, status,nocnt) values (1,'yes',3)
insert #tblA(ownerid, status,nocnt) values (1,'no',2)
insert #tblA(ownerid, status,nocnt) values (2,'missing',2)
insert #tblA(ownerid, status,nocnt) values (2,'yes',1)
insert #tblA(ownerid, status,nocnt) values (1,'missing',2)
insert #tblA(ownerid, status,nocnt) values (3,'yes',4)
insert #tblA(ownerid, status,nocnt) values (4,'missing',2)
insert #tblA(ownerid, status,nocnt) values (5,'no',6)
select ownerid,max(nocnt)
from(
select ownerid,
case when status='yes' then 0
else nocnt end as nocnt
from #tblA
where status='no' or status='yes')a
group by ownerid
April 14, 2005 at 5:41 pm
Thanks, Cliu. I knew there was a more elegant solution out there. I seem to have a mental block against using expressions in FROM clauses, I need to do some work to overcome that.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 15, 2005 at 7:30 am
Try this SQL:
select ownerid, SUM(case when status='yes' then 0
else nocnt end) as nocnt
from #tblA
where status IN ('no','yes')
group by ownerid
April 15, 2005 at 11:09 am
Unfortunately, Sebastiano, your code doesn't work, and I'm not certain why. It double-counts the NOs and produces incorrect results.
The results of your query produces this:
ownerid nocnt
-------- -----------
1 4
2 0
3 0
5 12
Sliu's query produces this, which is correct values:
ownerid NoCount
-------- -----------
1 2
2 0
3 0
5 6
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 17, 2005 at 11:12 am
I think the following select statement will give you what you want. There is a problem in that “count” does not include NULL values thus from the result set you will know that there is at least one record where the value of status is NULL (undetermined) but you will not know how many null values you have. Using the following:
create table #tblOwner(Ownerid int)
insert #tblOwner(Ownerid) values (1)
insert #tblOwner(Ownerid) values (2)
insert #tblOwner(Ownerid) values (3)
insert #tblOwner(Ownerid) values (4)
insert #tblOwner(Ownerid) values (5)
insert #tblOwner(Ownerid) values (6)
Create table #tblChild(ChildId int,OwnerID int,Status char(7))
insert #tblChild(ChildId,OwnerID,Status) values (1,1,'yes')
insert #tblChild(ChildId,OwnerID,Status) values (2,1,'yes')
insert #tblChild(ChildId,OwnerID,Status) values (3,1,'yes')
insert #tblChild(ChildId,OwnerID,Status) values (4,1,'No')
insert #tblChild(ChildId,OwnerID,Status) values (5,1,'No')
insert #tblChild(ChildId,OwnerID,Status) values (6,1,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (7,1,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (8,1,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (9,1,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (11,2,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (12,2,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (13,2,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (14,2,NULL)
insert #tblChild(ChildId,OwnerID,Status) values (15,3,'Yes')
insert #tblChild(ChildId,OwnerID,Status) values (16,3,'Yes')
insert #tblChild(ChildId,OwnerID,Status) values (16,3,'Yes')
insert #tblChild(ChildId,OwnerID,Status) values (17,3,'Yes')
insert #tblChild(ChildId,OwnerID,Status) values (18,3,'Yes')
insert #tblChild(ChildId,OwnerID,Status) values (19,3,'Yes')
insert #tblChild(ChildId,OwnerID,Status) values (20,3,'Yes')
insert #tblChild(ChildId,OwnerID,Status) values (21,4,'missing')
insert #tblChild(ChildId,OwnerID,Status) values (22,4,'missing')
SELECT o.OwnerID, c.Status,count(c.status)as Counts
FROM #tblOwner as o LEFT JOIN #tblChild as c ON c.OwnerID = o.OwnerID
WHERE (((c.Status)<> 'Yes')) OR (((c.Status) Is Null))
GROUP BY o.Ownerid,c.OwnerID,c.status;
Drop Table #tblOwner
Drop Table #tblChild
Returns the following result set
OwnerID Status
1 NULL 0
1 No 2
2 NULL 0
4 missing 2
5 NULL 0
6 NULL 0
HTH
Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply