Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Count for each account no -parent and childsql Expand / Collapse
Author
Message
Posted Wednesday, November 20, 2013 4:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:15 PM
Points: 13, Visits: 46
Hi Sean

The child account does not have a link code. It only has the child code.
so if I use this query for the whole dataset by just filtering on child code, it gives me the count of all the child codes instead of count of child codes belonging ot a particular account.

I need to write a query which somehow ties the child accounts to their parent account using the common account no.

Does that make sense?
Post #1516258
Posted Wednesday, November 20, 2013 9:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
nutty (11/20/2013)
Hi Guys
I have a data where there are parent and child product.
Each parent has got a unique code(P1) and a link code (L1) on their account
and each child of the parent has got a seperate code (C12) but they share the same account no.
I want to count the parent and children
Here is a sample dataset

Line_no code Account No
123 C12 Ac111
1222 C12 Ac111
1243 C12 Ac111
433 P1 Ac111
433 L1 Ac111
543 C1 Ac222
544 C1 Ac222
4322 P1 Ac222
4322 L1 Ac222

Now if I do a group by, I di get unique parents but not unique children

Please help!!


nutty (11/20/2013)
Hi
Apologies for not following the format. I will keep this in mind next time.

My desired output is

Parent_Count | Child_count
2 7


Thanks
Mita


nutty (11/20/2013)
Hi Sean

The child account does not have a link code. It only has the child code.
so if I use this query for the whole dataset by just filtering on child code, it gives me the count of all the child codes instead of count of child codes belonging ot a particular account.

I need to write a query which somehow ties the child accounts to their parent account using the common account no.

Does that make sense?


Yes but if you follow the progression of your posts, then "no". If you look at your first post and count all of the rows, you end up with 9 rows. 2 Parents and 7 "other" rows which include both Links (Lx rows) and Children (Cx rows). That happens to match exactly what you posted in the second quote above and Sean's code produces that output.

So my question is, according to the data in your original post, what should the output actually look like because, right now, I'm totally confused by your posts.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516304
Posted Thursday, November 21, 2013 10:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:15 PM
Points: 13, Visits: 46
Yes it does match what I want as output.
However the trouble is, these child codes are common codes for various other parent codes.
For example two different parents can share the same child codes and I have to find out which child code belongs to which parent and the only way i can do is through the account no because account nos will be different for each set of parent and child.

Am I clear this time? sorry to confuse you
Post #1516505
Posted Thursday, November 21, 2013 11:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 13,224, Visits: 12,068
nutty (11/21/2013)
Yes it does match what I want as output.
However the trouble is, these child codes are common codes for various other parent codes.
For example two different parents can share the same child codes and I have to find out which child code belongs to which parent and the only way i can do is through the account no because account nos will be different for each set of parent and child.

Am I clear this time? sorry to confuse you


So does the code I posted get you what you need or not? I am quite confused at this point.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1516538
Posted Thursday, November 21, 2013 2:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
nutty (11/21/2013)
Yes it does match what I want as output.
However the trouble is, these child codes are common codes for various other parent codes.
For example two different parents can share the same child codes and I have to find out which child code belongs to which parent and the only way i can do is through the account no because account nos will be different for each set of parent and child.

Am I clear this time? sorry to confuse you


Ah. Understood. Add some data to the test data generation code on this thread and post it so folks can take a crack at it for you.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516590
Posted Thursday, November 21, 2013 5:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:15 PM
Points: 13, Visits: 46
Hi Jeff

Here is what I want:

create table #Something
(
Line_no int,
code varchar(5),
AccountNo char(5)
)

insert #Something
select 12311, 'P1c', 'Ac115' union all
select 12311, 'L1', 'Ac115' union all
select 123, 'C1', 'Ac115' union all
select 1222, 'C1', 'Ac115' union all
select 1243, 'C1', 'Ac115' union all
select 433, 'P1a', 'Ac111' union all
select 433, 'L1', 'Ac111' union all
select 4331, 'C1', 'Ac111' union all
select 543, 'C1', 'Ac222' union all
select 544, 'C1', 'Ac222' union all
select 4322, 'P1b', 'Ac222' union all
select 4322, 'L1', 'Ac222' union all
select 8766 'P1d' , 'Ab111' union all
select 8767 'C1', 'Ab111' union all
select 8789 'C1', ' Ab111' union all
select 8766 'L1', ' Ab111'

select * from #Something

drop table #Something


Desired output is:

[Parent code] [Parent line Count] [Child line Count]

P1c 1 3
P1a 1 1
P1b 1 2
P1d 1 2
Post #1516614
Posted Thursday, November 21, 2013 6:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 3,618, Visits: 5,254
Your test data is flawed because it is missing some commas in the INSERT/SELECT and you get truncation errors putting it into the data types you supplied in the CREATE TABLE (see comment below). Nonetheless, this runs:

create table #Something
(
Line_no int,
code varchar(5),
AccountNo char(5)
)

insert #Something
select 12311, 'P1c', 'Ac115' union all
select 12311, 'L1', 'Ac115' union all
select 123, 'C1', 'Ac115' union all
select 1222, 'C1', 'Ac115' union all
select 1243, 'C1', 'Ac115' union all
select 433, 'P1a', 'Ac111' union all
select 433, 'L1', 'Ac111' union all
select 4331, 'C1', 'Ac111' union all
select 543, 'C1', 'Ac222' union all
select 544, 'C1', 'Ac222' union all
select 4322, 'P1b', 'Ac222' union all
select 4322, 'L1', 'Ac222' union all
select 8766, 'P1d' , 'Ab111' union all
select 8767, 'C1', 'Ab111' union all
select 8789, 'C1', 'Ab111' union all -- last 2 rows had leading space in last column
select 8766, 'L1', 'Ab111'


And this delivers your expected results:

SELECT [Parent code]=a.code
,[Parent Line Count]=COUNT(a.Line_no) OVER (PARTITION BY a.code)
,[Child Line Count]=COUNT(*)
FROM #Something a
JOIN #Something b ON a.Line_No = b.Line_No AND
LEFT(a.code, 1) = 'P' AND LEFT(b.code, 1) = 'L'
JOIN #Something c ON b.AccountNo = c.AccountNo AND
LEFT(c.code, 1) NOT IN ('P', 'L')
GROUP BY a.code, a.Line_no;


However, none of your test data is constructed for a case where the [Parent Line Count] may be > 1. So I must leave that up to you to test.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1516619
Posted Friday, November 22, 2013 3:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:15 PM
Points: 13, Visits: 46
Hi
Thanks for the code.
You are right.
I have multiple lines of same parent code in my database.
How do I deal with that? ?
Pls help !!!
Post #1516699
Posted Friday, November 22, 2013 3:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 3,618, Visits: 5,254
nutty (11/22/2013)
Hi
Thanks for the code.
You are right.
I have multiple lines of same parent code in my database.
How do I deal with that? ?
Pls help !!!


Expand your test data and give us the expected results.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1516710
Posted Friday, November 22, 2013 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:15 PM
Points: 13, Visits: 46
Here it is

create table #Something
(
Line_no int,
code varchar(5),
AccountNo char(5)
)

insert #Something
select 12311, 'P1c', 'Ac115' union all
select 12311, 'L1', 'Ac115' union all
select 123, 'C1', 'Ac115' union all
select 1222, 'C1', 'Ac115' union all
select 1243, 'C1', 'Ac115' union all
select 433, 'P1a', 'Ac111' union all
select 433, 'L1', 'Ac111' union all
select 4331, 'C1', 'Ac111' union all
select 543, 'C1', 'Ac222' union all
select 544, 'C1', 'Ac222' union all
select 4322, 'P1b', 'Ac222' union all
select 4322, 'L1', 'Ac222' union all

select 8766 'P1d' , 'Ab111' union all
select 8766 'L1' , 'Ab111' union all
select 8767 'C1', 'Ab111' union all
select 8789 'P1d', 'Ab119' union all
select 8766 'L1', 'Ab119' union all
select 876654 'C1', 'Ab119' union all
select 876655 'C1', 'Ab119' union all
select 876698 'P1a', 'Ab117' union all
select 876698 'L1', 'Ab117' union all
select 987 'C1', 'Ab117' union all
select 555444 'P1d' 'Xcv' union all
select 555444 'L1' 'Xcv' union all
select 6754 'C1' 'Xcv' union all

select * from #Something

drop table #Something


Desired output is:

[Parent code] [Parent line Count] [Child line Count]

P1c 1 3
P1a 2 2
P1b 1 2
P1d 3 4
Post #1516895
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse