count unique nodes
in [Server Configuration]
|
Prev: Cannot find a particular record by searching on non-primary key
Next: Help with counting values that have different endings
From: Eric on 11 Jun 2010 09:56 Hi, I have a table that looks like this: Aside1 Aside2 Aside3 Aside4 Aside5 Aside6 Aside7 S0009 S1416 S0009 S5205 S5217 S0009 S5205 S0009 S5552 SWITCH-ASD S0009 S0004 S0010 S1306 SWITCH-ASD S0009 S0004 S0010 SWITCH-ASD S0009 S0004 S0077 S3139 SWITCH-ASD S0009 S0004 S0077 S3526 SWITCH-ASD S0009 S0004 S0077 SWITCH-ASD S0009 S0004 S1507 SWITCH-ASD S0009 S0004 S1662 SWITCH-ASD S0009 S0004 S2458 SWITCH-ASD S0009 S0004 S3518 SWITCH-ASD S0009 S0004 SWITCH-ASD S0009 S0015 S0014 S4918 SWITCH-ASD S0009 S0015 S0014 SWITCH-ASD S0009 S0015 S1511 S2620 SWITCH-ASD S0009 S0015 S1511 SWITCH-ASD S0009 S0015 S1797 SWITCH-ASD S0009 S0015 S2504 SWITCH-ASD S0009 S0015 S3136 SWITCH-ASD S0009 S0015 SWITCH-ASD S0009 S0659 S1503 SWITCH-ASD S0009 S0659 S2020 S7700 SWITCH-ASD S0009 S0659 S2020 SWITCH-ASD S0009 S0659 S3119 SWITCH-ASD S0009 S0659 SWITCH-ASD S0009 S3110 SWITCH-ASD S0009 S3446 SWITCH-ASD S0009 S3454 SWITCH-ASD S0009 This is what you get after a select command on node S0009 existing in any of the columns. Now I need to know how many unique nodes there are behind S0009. Manually counting, this should be: 28. I have been puzzling all afternoon here, but I can't figure it out. Maybe it's not possible but probably my knowledge of SQL is not enough. Anyone who could give me a hand on this? Any help is highly appreciated. rg, Eric
From: John Bell on 12 Jun 2010 16:03 On Fri, 11 Jun 2010 06:56:00 -0700, Eric <Eric(a)discussions.microsoft.com> wrote: >Hi, > >I have a table that looks like this: > >Aside1 Aside2 Aside3 Aside4 Aside5 Aside6 Aside7 >S0009 S1416 >S0009 S5205 S5217 >S0009 S5205 >S0009 S5552 >SWITCH-ASD S0009 S0004 S0010 S1306 >SWITCH-ASD S0009 S0004 S0010 >SWITCH-ASD S0009 S0004 S0077 S3139 >SWITCH-ASD S0009 S0004 S0077 S3526 >SWITCH-ASD S0009 S0004 S0077 >SWITCH-ASD S0009 S0004 S1507 >SWITCH-ASD S0009 S0004 S1662 >SWITCH-ASD S0009 S0004 S2458 >SWITCH-ASD S0009 S0004 S3518 >SWITCH-ASD S0009 S0004 >SWITCH-ASD S0009 S0015 S0014 S4918 >SWITCH-ASD S0009 S0015 S0014 >SWITCH-ASD S0009 S0015 S1511 S2620 >SWITCH-ASD S0009 S0015 S1511 >SWITCH-ASD S0009 S0015 S1797 >SWITCH-ASD S0009 S0015 S2504 >SWITCH-ASD S0009 S0015 S3136 >SWITCH-ASD S0009 S0015 >SWITCH-ASD S0009 S0659 S1503 >SWITCH-ASD S0009 S0659 S2020 S7700 >SWITCH-ASD S0009 S0659 S2020 >SWITCH-ASD S0009 S0659 S3119 >SWITCH-ASD S0009 S0659 >SWITCH-ASD S0009 S3110 >SWITCH-ASD S0009 S3446 >SWITCH-ASD S0009 S3454 >SWITCH-ASD S0009 > >This is what you get after a select command on node S0009 existing in any of >the columns. > >Now I need to know how many unique nodes there are behind S0009. >Manually counting, this should be: 28. > >I have been puzzling all afternoon here, but I can't figure it out. >Maybe it's not possible but probably my knowledge of SQL is not enough. > >Anyone who could give me a hand on this? >Any help is highly appreciated. > >rg, >Eric > Eric Always post DDL and example data as insert statements such as CREATE TABLE Asides ( Aside1 varchar(20), Aside2 varchar(20), Aside3 varchar(20), Aside4 varchar(20), Aside5 varchar(20), Aside6 varchar(20), Aside7 varchar(20) ) INSERT INTO Asides ( Aside1, Aside2, Aside3, Aside4, Aside5 ) VALUES ('S0009', 'S1416', NULL, NULL, NULL ), ('S0009', 'S5205', 'S5217', NULL, NULL), ('S0009', 'S5205', NULL, NULL, NULL ), ('S0009', 'S5552', NULL, NULL, NULL ), ('SWITCH-ASD', 'S0009', 'S0004', 'S0010', 'S1306'), ('SWITCH-ASD', 'S0009', 'S0004', 'S0010', NULL ), ('SWITCH-ASD', 'S0009', 'S0004', 'S0077', 'S3139'), ('SWITCH-ASD', 'S0009', 'S0004', 'S0077', 'S3526'), ('SWITCH-ASD', 'S0009', 'S0004', 'S0077', NULL ), ('SWITCH-ASD', 'S0009', 'S0004', 'S1507', NULL ), ('SWITCH-ASD', 'S0009', 'S0004', 'S1662', NULL ), ('SWITCH-ASD', 'S0009', 'S0004', 'S2458', NULL ), ('SWITCH-ASD', 'S0009', 'S0004', 'S3518', NULL ), ('SWITCH-ASD', 'S0009', 'S0004', NULL , NULL ), ('SWITCH-ASD', 'S0009', 'S0015', 'S0014', 'S4918'), ('SWITCH-ASD', 'S0009', 'S0015', 'S0014', NULL ), ('SWITCH-ASD', 'S0009', 'S0015', 'S1511', 'S2620'), ('SWITCH-ASD', 'S0009', 'S0015', 'S1511', NULL ), ('SWITCH-ASD', 'S0009', 'S0015', 'S1797', NULL ), ('SWITCH-ASD', 'S0009', 'S0015', 'S2504', NULL ), ('SWITCH-ASD', 'S0009', 'S0015', 'S3136', NULL ), ('SWITCH-ASD', 'S0009', 'S0015', NULL , NULL ), ('SWITCH-ASD', 'S0009', 'S0659', 'S1503', NULL ), ('SWITCH-ASD', 'S0009', 'S0659', 'S2020', 'S7700'), ('SWITCH-ASD', 'S0009', 'S0659', 'S2020', NULL ), ('SWITCH-ASD', 'S0009', 'S0659', 'S3119', NULL ), ('SWITCH-ASD', 'S0009', 'S0659', NULL , NULL ), ('SWITCH-ASD', 'S0009', 'S3110', NULL , NULL ), ('SWITCH-ASD', 'S0009', 'S3446', NULL , NULL ), ('SWITCH-ASD', 'S0009', 'S3454', NULL , NULL ), ('SWITCH-ASD', 'S0009', NULL , NULL , NULL ) I initially believed from your description the following is what you want: SELECT COUNT(DISTINCT ISNULL(Aside1,'') + '|' + ISNULL(Aside2,'') + '|' + ISNULL(Aside3,'') + '|' + ISNULL(Aside4,'') + '|' + ISNULL(Aside5,'') + '|' + ISNULL(Aside6,'') + '|' + ISNULL( Aside7,'') ) FROM Asides WHERE Aside1 = 'S0009' OR Aside2 = 'S0009' OR Aside3 = 'S0009' OR Aside4 = 'S0009' OR Aside5 = 'S0009' OR Aside6 = 'S0009' OR Aside7 = 'S0009' but wuith my sample data is gives 31. But re-reading the post it was not clear what you mean by after so it could be something like: SELECT COUNT(DISTINCT Aside) FROM ( SELECT Aside2 AS Aside FROM Asides WHERE Aside1 = 'S0009' UNION SELECT Aside3 FROM Asides WHERE Aside1 = 'S0009' UNION SELECT Aside4 FROM Asides WHERE Aside1 = 'S0009' UNION SELECT Aside5 FROM Asides WHERE Aside1 = 'S0009' UNION SELECT Aside6 FROM Asides WHERE Aside1 = 'S0009' UNION SELECT Aside7 FROM Asides WHERE Aside1 = 'S0009' UNION SELECT Aside3 FROM Asides WHERE Aside2 = 'S0009' UNION SELECT Aside4 FROM Asides WHERE Aside2 = 'S0009' UNION SELECT Aside5 FROM Asides WHERE Aside2 = 'S0009' UNION SELECT Aside6 FROM Asides WHERE Aside2 = 'S0009' UNION SELECT Aside7 FROM Asides WHERE Aside2 = 'S0009' UNION SELECT Aside4 FROM Asides WHERE Aside3 = 'S0009' UNION SELECT Aside5 FROM Asides WHERE Aside3 = 'S0009' UNION SELECT Aside6 FROM Asides WHERE Aside3 = 'S0009' UNION SELECT Aside7 FROM Asides WHERE Aside3 = 'S0009' UNION SELECT Aside5 FROM Asides WHERE Aside4 = 'S0009' UNION SELECT Aside6 FROM Asides WHERE Aside4 = 'S0009' UNION SELECT Aside7 FROM Asides WHERE Aside4 = 'S0009' UNION SELECT Aside6 FROM Asides WHERE Aside5 = 'S0009' UNION SELECT Aside7 FROM Asides WHERE Aside5 = 'S0009' UNION SELECT Aside7 FROM Asides WHERE Aside6 = 'S0009' ) A but that gives 30 John
From: Eric on 14 Jun 2010 11:07
Thank you for you answer. I may have counted wrong, so your solutions might be correct. rg, Eric "John Bell" wrote: > On Fri, 11 Jun 2010 06:56:00 -0700, Eric > <Eric(a)discussions.microsoft.com> wrote: > > >Hi, > > > >I have a table that looks like this: > > > >Aside1 Aside2 Aside3 Aside4 Aside5 Aside6 Aside7 > >S0009 S1416 > >S0009 S5205 S5217 > >S0009 S5205 > >S0009 S5552 > >SWITCH-ASD S0009 S0004 S0010 S1306 > >SWITCH-ASD S0009 S0004 S0010 > >SWITCH-ASD S0009 S0004 S0077 S3139 > >SWITCH-ASD S0009 S0004 S0077 S3526 > >SWITCH-ASD S0009 S0004 S0077 > >SWITCH-ASD S0009 S0004 S1507 > >SWITCH-ASD S0009 S0004 S1662 > >SWITCH-ASD S0009 S0004 S2458 > >SWITCH-ASD S0009 S0004 S3518 > >SWITCH-ASD S0009 S0004 > >SWITCH-ASD S0009 S0015 S0014 S4918 > >SWITCH-ASD S0009 S0015 S0014 > >SWITCH-ASD S0009 S0015 S1511 S2620 > >SWITCH-ASD S0009 S0015 S1511 > >SWITCH-ASD S0009 S0015 S1797 > >SWITCH-ASD S0009 S0015 S2504 > >SWITCH-ASD S0009 S0015 S3136 > >SWITCH-ASD S0009 S0015 > >SWITCH-ASD S0009 S0659 S1503 > >SWITCH-ASD S0009 S0659 S2020 S7700 > >SWITCH-ASD S0009 S0659 S2020 > >SWITCH-ASD S0009 S0659 S3119 > >SWITCH-ASD S0009 S0659 > >SWITCH-ASD S0009 S3110 > >SWITCH-ASD S0009 S3446 > >SWITCH-ASD S0009 S3454 > >SWITCH-ASD S0009 > > > >This is what you get after a select command on node S0009 existing in any of > >the columns. > > > >Now I need to know how many unique nodes there are behind S0009. > >Manually counting, this should be: 28. > > > >I have been puzzling all afternoon here, but I can't figure it out. > >Maybe it's not possible but probably my knowledge of SQL is not enough. > > > >Anyone who could give me a hand on this? > >Any help is highly appreciated. > > > >rg, > >Eric > > > > Eric > > Always post DDL and example data as insert statements such as > > CREATE TABLE Asides ( Aside1 varchar(20), Aside2 varchar(20), > Aside3 varchar(20), Aside4 varchar(20), Aside5 varchar(20), > Aside6 varchar(20), Aside7 varchar(20) ) > > INSERT INTO Asides ( Aside1, Aside2, Aside3, Aside4, Aside5 ) > VALUES ('S0009', 'S1416', NULL, NULL, NULL ), > ('S0009', 'S5205', 'S5217', NULL, NULL), > ('S0009', 'S5205', NULL, NULL, NULL ), > ('S0009', 'S5552', NULL, NULL, NULL ), > ('SWITCH-ASD', 'S0009', 'S0004', 'S0010', 'S1306'), > ('SWITCH-ASD', 'S0009', 'S0004', 'S0010', NULL ), > ('SWITCH-ASD', 'S0009', 'S0004', 'S0077', 'S3139'), > ('SWITCH-ASD', 'S0009', 'S0004', 'S0077', 'S3526'), > ('SWITCH-ASD', 'S0009', 'S0004', 'S0077', NULL ), > ('SWITCH-ASD', 'S0009', 'S0004', 'S1507', NULL ), > ('SWITCH-ASD', 'S0009', 'S0004', 'S1662', NULL ), > ('SWITCH-ASD', 'S0009', 'S0004', 'S2458', NULL ), > ('SWITCH-ASD', 'S0009', 'S0004', 'S3518', NULL ), > ('SWITCH-ASD', 'S0009', 'S0004', NULL , NULL ), > ('SWITCH-ASD', 'S0009', 'S0015', 'S0014', 'S4918'), > ('SWITCH-ASD', 'S0009', 'S0015', 'S0014', NULL ), > ('SWITCH-ASD', 'S0009', 'S0015', 'S1511', 'S2620'), > ('SWITCH-ASD', 'S0009', 'S0015', 'S1511', NULL ), > ('SWITCH-ASD', 'S0009', 'S0015', 'S1797', NULL ), > ('SWITCH-ASD', 'S0009', 'S0015', 'S2504', NULL ), > ('SWITCH-ASD', 'S0009', 'S0015', 'S3136', NULL ), > ('SWITCH-ASD', 'S0009', 'S0015', NULL , NULL ), > ('SWITCH-ASD', 'S0009', 'S0659', 'S1503', NULL ), > ('SWITCH-ASD', 'S0009', 'S0659', 'S2020', 'S7700'), > ('SWITCH-ASD', 'S0009', 'S0659', 'S2020', NULL ), > ('SWITCH-ASD', 'S0009', 'S0659', 'S3119', NULL ), > ('SWITCH-ASD', 'S0009', 'S0659', NULL , NULL ), > ('SWITCH-ASD', 'S0009', 'S3110', NULL , NULL ), > ('SWITCH-ASD', 'S0009', 'S3446', NULL , NULL ), > ('SWITCH-ASD', 'S0009', 'S3454', NULL , NULL ), > ('SWITCH-ASD', 'S0009', NULL , NULL , NULL ) > > I initially believed from your description the following is what you > want: > > SELECT COUNT(DISTINCT ISNULL(Aside1,'') + '|' + ISNULL(Aside2,'') + > '|' + ISNULL(Aside3,'') + '|' + ISNULL(Aside4,'') + '|' + > ISNULL(Aside5,'') + '|' + ISNULL(Aside6,'') + '|' + ISNULL( Aside7,'') > ) > FROM Asides > WHERE Aside1 = 'S0009' OR > Aside2 = 'S0009' OR > Aside3 = 'S0009' OR > Aside4 = 'S0009' OR > Aside5 = 'S0009' OR > Aside6 = 'S0009' OR > Aside7 = 'S0009' > > but wuith my sample data is gives 31. > > But re-reading the post it was not clear what you mean by after > so it could be something like: > > SELECT COUNT(DISTINCT Aside) > FROM ( > SELECT Aside2 AS Aside > FROM Asides > WHERE Aside1 = 'S0009' > UNION > SELECT Aside3 > FROM Asides > WHERE Aside1 = 'S0009' > UNION > SELECT Aside4 > FROM Asides > WHERE Aside1 = 'S0009' > UNION > SELECT Aside5 > FROM Asides > WHERE Aside1 = 'S0009' > UNION > SELECT Aside6 > FROM Asides > WHERE Aside1 = 'S0009' > UNION > SELECT Aside7 > FROM Asides > WHERE Aside1 = 'S0009' > > UNION > SELECT Aside3 > FROM Asides > WHERE Aside2 = 'S0009' > UNION > SELECT Aside4 > FROM Asides > WHERE Aside2 = 'S0009' > UNION > SELECT Aside5 > FROM Asides > WHERE Aside2 = 'S0009' > UNION > SELECT Aside6 > FROM Asides > WHERE Aside2 = 'S0009' > UNION > SELECT Aside7 > FROM Asides > WHERE Aside2 = 'S0009' > > UNION > SELECT Aside4 > FROM Asides > WHERE Aside3 = 'S0009' > UNION > SELECT Aside5 > FROM Asides > WHERE Aside3 = 'S0009' > UNION > SELECT Aside6 > FROM Asides > WHERE Aside3 = 'S0009' > UNION > SELECT Aside7 > FROM Asides > WHERE Aside3 = 'S0009' > > UNION > SELECT Aside5 > FROM Asides > WHERE Aside4 = 'S0009' > UNION > SELECT Aside6 > FROM Asides > WHERE Aside4 = 'S0009' > UNION > SELECT Aside7 > FROM Asides > WHERE Aside4 = 'S0009' > > UNION > SELECT Aside6 > FROM Asides > WHERE Aside5 = 'S0009' > UNION > SELECT Aside7 > FROM Asides > WHERE Aside5 = 'S0009' > > UNION > SELECT Aside7 > FROM Asides > WHERE Aside6 = 'S0009' > ) A > > but that gives 30 > > John > . > |