Count occurances of value from a table 1

[Sarky78]

Hi All,

I'm looking to query a hierarchical table that i have written, that has a format like this:

OffenceID     ParentID     OffenceName
1                0         ParentOffence1
2                1         ChildOffence1
3                2         GrandChild1
4                2         GrandChild2
5                2         GrandChild3
6                0         ParentOffence2
7                6         ChildOffence2
8                7         GrandChild4
9                7         Childoffence3
10               9         GrandChild5

I have a second table that references the above, and people can select the lower level identified above as GrandChild1/2/3 etc.

What i need to be able to do, is to get a count of how many times the lower level items appear within the other table, and group them by their parent.

so if items, 3,4 and 5 appear in the other table 2,5 and 6 times respectively, i want to be able to present ParentOffence1 (13) and so on down the list. I've seen the FAQ here:

http://www.tek-tips.com/faqs.cfm?fid=5322

but to be honest can't make much sense of it.

Can anyone help out?

Thanks

Tony

 

[simian336]

Can you post a data example from the other table and expected results.

Simi

 

[Sarky78]

Hey Simi,

The other table contains information like:

ID OffenceID
1 5
2 4
3 3
4 4
5 5
6 10

I need the output to be:


ParentOffence1 (5)
ParentOffence2 (1)

and so on for the other parent offences in the list if one of the lower level offences exists within the other table.

Thanks

Tony

 

[Sarky78]

Anyone got any good ideas on how i can achieve this?

Thanks

Tony

 

[simian336]

Sorry, I am still not sure I follow. Could you take one more stab at explaining the problem and expected solution.

Thanks

Simi

 

[Sarky78]

Simi,

Sorry, I find it difficult to explain as well to be honest.

So I have a database structure something like this:

PhoneID     ParentID     PhoneName
1                0       Nokia
2                1       3G
3                1       2G
4                2       Phone1
5                2       Phone2
6                0       Motorola
7                6       3G
8                7       Phone3
9                7       Phone4
10               0       Apple
11               10      Phone5
12               10      Phone6

The parent groups are the ones that have a 0 as the ParentID

This table is a lookup table for the main data table used within the system, and produces a drop down list within an administration system like this:

[b]Nokia(1)[/b]
--[b]3G(2)[/b]
----Phone1(4)
----Phone2(5)
--[b]2G(3)[/b]
[b]Motorola(6)[/b]
--[b]3G(7)[/b]
----Phone3(8)
----Phone4(9)
[b]Apple(10)[/b]
----Phone5(11)
----Phone6(12)

Notice that the Apple node only doesn't have the sub classification (3g/2g) on it, which adds to the fun!

The items in bold the user can't select as options for the data being entered, they are just there for classification purposes, within the administration system. The value in brackets is just there to help illustrate, and it is this value that goes into the main data table. The value can be associated with multiple records within the main data table.

Within the front end of the system the "parent" make of the phone (Nokia, Motorola etc.) is what i want to display to the user, with the number of occurrences of the child and grandchild ID's from the main data table added together and then displayed.

So if in my main data table i have something like:

OfferID     PhoneID
1              4
2              4
3              5
4              5
5              8
6              8
7              8
8              9
9              9
10             9
11             11
12             11
13             11
14             12
15             12
16             12

The output that I want to achieve with the above situation is:

Nokia (4)
Motorola (6)
Apple (6)

Where the number in brackets is the number of occurrences of the parent group within the data table

Hoping this makes a bit more sense

Do you think this is going to be achievable?

Tony

 

[simian336]

ok.... Not crazy about your layout but it might work....

I did not know the names of you tables so I just named them phone and phone2.

--3 level matrix of your phone listing.
select *
from phone p1
left join phone p2
on p1.phoneid=p2.parentid
left join phone p3
on p2.phoneid=p3.parentid
where p1.parentid = 0


-- count of your 3 level matrix
with level3 as
(
select p1.phoneid as l1, p1.phonename as l1name,
p2.phoneid as l2,
p3.phoneid as l3
from phone p1
left join phone p2
on p1.phoneid=p2.parentid
left join phone p3
on p2.phoneid=p3.parentid
where p1.parentid = 0
and p3.phoneid is not null
)
select l.l1name, count(l.l1name)
from level3 l
join phone2 p
on l.l3= p.phoneid
group by l.l1name

--result

Motorola 6
Nokia 4

You will just need to do the same as above for the 2 levels (anouther reason not to like Apple : ) ) and union them together.

Simi

 

[simian336]

with level2 as
(
select p1.phoneid as l1, p1.phonename as l1name,
p2.phoneid as l2,
p3.phoneid as l3
from phone p1
left join phone p2
on p1.phoneid=p2.parentid
left join phone p3
on p2.phoneid=p3.parentid
where p1.parentid = 0
and p3.phoneid is null
)
select l.l1name, count(l.l1name)
from level2 l
join phone2 p
on l.l2= p.phoneid
group by l.l1name;

I was not able to get those to union perhaps someone will know the answer to that.

Simi

 

[MarkXaml]

It's not pretty at all, but it appears to work. I modified some values and the outcome changed as expected. I got the idea from here and tweaked it. I'm not sure how performant it would be in a very large table... but it should handle any number of children/grandchildren.

DECLARE @tblHierarchical TABLE (PhoneID int identity(1,1),parentid int, PhoneName varchar(50))
insert into @tblHierarchical(parentid, PhoneName)values(0,'Nokia')
insert into @tblHierarchical(parentid, PhoneName)values(1,'3G')
insert into @tblHierarchical(parentid, PhoneName)values(1,'2G')
insert into @tblHierarchical(parentid, PhoneName)values(2,'Phone1')
insert into @tblHierarchical(parentid, PhoneName)values(2,'Phone2')
insert into @tblHierarchical(parentid, PhoneName)values(0,'Motorola')
insert into @tblHierarchical(parentid, PhoneName)values(6,'3G')
insert into @tblHierarchical(parentid, PhoneName)values(7,'Phone3')
insert into @tblHierarchical(parentid, PhoneName)values(7,'Phone4')
insert into @tblHierarchical(parentid, PhoneName)values(0,'Apple')
insert into @tblHierarchical(parentid, PhoneName)values(10,'Phone5')
insert into @tblHierarchical(parentid, PhoneName)values(10,'Phone6')

DECLARE @tblMainData TABLE (offerid int identity(1,1), phoneid int)
insert into @tblMainData (phoneid) values (4)
insert into @tblMainData (phoneid) values (4)
insert into @tblMainData (phoneid) values (5)
insert into @tblMainData (phoneid) values (5)
insert into @tblMainData (phoneid) values (8)
insert into @tblMainData (phoneid) values (8)
insert into @tblMainData (phoneid) values (8)
insert into @tblMainData (phoneid) values (9)
insert into @tblMainData (phoneid) values (9)
insert into @tblMainData (phoneid) values (9)
insert into @tblMainData (phoneid) values (11)
insert into @tblMainData (phoneid) values (11)
insert into @tblMainData (phoneid) values (11)
insert into @tblMainData (phoneid) values (12)
insert into @tblMainData (phoneid) values (12)
insert into @tblMainData (phoneid) values (12)

;WITH Hierarchy (PhoneName, PhoneID, parentid, [Level], trail) AS
(
   -- Base case
	SELECT			PhoneName,
					PhoneID,
					parentid,
					0 as [Level],
					'/' + cast(PhoneID as varchar(max))+ '/' as trail		
	FROM			@tblHierarchical th
	WHERE			th.parentid = 0

   UNION ALL

   -- Recursive step
   SELECT			th2.PhoneName,
					th2.PhoneID,
					th2.parentid,
					h.[Level] + 1,
					trail + CAST(th2.PhoneID as varchar(max)) + '/'
	FROM			@tblHierarchical th2
   INNER JOIN		Hierarchy h ON
         h.PhoneID = th2.parentid 
)



SELECT phoneParent.Parent, COUNT(phoneParent.Parent)
FROM	@tblMainData main
INNER JOIN	(select h2.PhoneName,h.PhoneName as Parent, h2.PhoneID 
			from Hierarchy h2, Hierarchy h
			where charindex('/' + CAST(h.PhoneID as varchar(max)) + '/', h2.trail, 1) <> 0
			and h2.Level > 0 
			and h.Level = 0) phoneParent
ON		main.phoneid = phoneParent.PhoneID
GROUP BY	phoneParent.Parent

Good luck! This post intrigued me, so I'm curious to see what comes of it.

--Mark

 

[Sarky78]

Hey Both,

Thanks for your responses.

Simi, you solution worked a treat (hence the stars, shame i can only give one!)

What i have done, is to have two stored procedures, with the code from above, and then a third procedure that combines the data into a temp table to allow me to do other stuff. Works a treat. Might not be the most glamorous of solutions, but it is working, and i am going to be caching the data for a day within me c# app so all good.

Thanks

Tony