How to Get X and Y Count - 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

How to Get X and Y Count

Author

Topic

manish_a_panchal
Starting Member

3 Posts

Posted - 2011-02-01 : 01:37:33

Hi all I am new in SQL and I have one problem with my current project.

I have one table in which I have following Fields and DATA

Customer_ID int
Customer_Name VarChar(100)
Sponser_ID int
GenX int
GenY int

Data
-----
Customer_ID Customer_Name Sponser_ID GenX GenY
1 Manish Panchal NULL 2 3
2 Akhilesh 1 4 5
3 Pooja 2 6 7
4 Alpesh 2 8 10
5 Mayur 2 9 11
6 Rakesh 3 12 Null
7 Manoj 3 Null Null
8 Shailesh 4 Null Null
9 Jitendra 5 Null Null
10 Yajuvendra 4 Null Null
11 Hiren 5 Null Null

and I want the following result.

Customer_Name GenXCount GenYCount
Manish 7 4
Akhilesh 3 3
Pooja 2 1
Alpesh 1 1
Mayur 1 1
Rakesh 1 0
Manoj 0 0

Please help me in this....

Manish Panchal
.NET Developer

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-01 : 01:43:16

a bit confusing .... as per original information "2 Akhilesh 1 4 5" this person has 4,5 as GenX and GenY respectively. However as per required output the counts are 3,3 for GenY and GenY. Why is this so as i am unable to see this record is repeated in the mentioned table's data. can you explain it

manish_a_panchal
Starting Member

3 Posts

Posted - 2011-02-01 : 02:02:10

Hi MIK,

I can exlplain you

2 is Customer_ID of Akhilesh
1 is Sponser_ID of akhilesh which is Customer_ID of Manish Panchal(means Manish is sponser of akhilesh)
4 is GenX of Akhilesh which just give information that Akhilesh having GenX which having customer_Id 4

5 is GenY of Akhilesh which just give information, that Akhilesh having GenY which having customer_Id 5

so when I want to see that Manish Panchal Having how many GenX and GenY

then it should shows me GenX - 1 and GenY - 1 as you see in my thread

now Akhilesh Having made on GenX and GenY then Manish Panchal's GenX count become 3 because Akhilesh is GenX of Manish Panchal...

is this make sense for you or I can Explain more....

Manish Panchal
.NET Developer

manish_a_panchal
Starting Member

3 Posts

Posted - 2011-02-01 : 03:11:36

My question is If I want to know that how many GenX and How many GenY Manishpanchal have then how Can i know it?

Second question is output will have to show every member's GenX and GenY count who are under Manish Panchal's Sponser_ID

For Example:

If Manish Panchal have made one Member in GenX who have Customer_id 2 and One member in GenY who have Customer_id 3.

Now when Customer_ID 2 made one GenX and one GenY then Manish's GenX count become 3 because Customer_ID 2 is GenX of Manish Panchal means Customer_ID 2 and their nodes.
When Customer_ID 3 will make One GenX and One GenY then Manish's GenY count become 3.

this will applied to all members which are in hirarchy

Manish Panchal
.NET Developer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources