Click here to Skip to main content
15,881,588 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello coder, I have below mentioned kind of data. I want some customised output please help.

Cust no PAN Name Ac Balance
1.            Aaa. Joy.    1.   50
2.            BBB. Kevin. 2.   100
3.            Aaa. Peter 3.    50


I want to group by on pan at the same time required name of the lower customer number.
Expected out put
Pan count name balance
Aaa.   2.      Joy    100
BBB.   1.      Kevin 100


What I have tried:

Able to achieve data without name
Posted
Updated 11-Jul-20 21:48pm
v4
Comments
Patrice T 12-Jul-20 1:48am    
Your sample data seems not be related, update data or give exact rules you use/want for output.
Show your actual work.
Hemil Gandhi 12-Jul-20 3:49am    
Question improved, please check now
sorry for the confusion

1 solution

The data your show us can't be GROUPed to give the solution you ask for, because there is no "common data" which combines rows 1 and 2 but not 3, or rows 1 and 3 but changes the name on 2.

You need to look at what you are trying to do a bit more closely: I'm pretty sure both your input and output data examples are wrong: shouldn't "Aaaa." be "Aaa." in the input and "Peter" be "Kevin" in the output?

If so, it's a simple GROUP BY and a JOIN to get the data you want.


Since it's clear you don't want to show us what you have tried, and equally clear that this is homework, I'm not going to give you the code.

Instead, I'll explain how to get the code you want.
Start by writing a SELECT using the GROUP BY:
SELECT ... fields ... 
FROM MyTable
GROUP BY <grouping column>
YOur grouping column will obviously be PAN, and the fields will be the grouping column, the combined balance, the lowest customer number, and the number of rows in the group.
That's pretty trivial: test it and check you have the right results:
PAN     Balance  CustNo  Count
Aaa.        100	      1      2
BBB.        100       2      1

Then all you need is to use a JOIN to combine that with your original table to get the Name and you are done.

Simple - so give it a try, and show us what you ended up with.
 
Share this answer
 
v3
Comments
Hemil Gandhi 12-Jul-20 1:57am    
You are correct I have mistakenly add one more a in "Aaaa". & I want name having lower customer I'd. Name are not similar that is my main issue while doing group by.
Hope I appropriately explained my self
Patrice T 12-Jul-20 2:32am    
Use Improve question to update your question.
So that everyone can pay attention to this information.
OriginalGriff 12-Jul-20 3:14am    
Right - so what have you tried?
OriginalGriff 12-Jul-20 3:15am    
And how did "BBB" become "Peter" instead of "Kevin"?
Hemil Gandhi 12-Jul-20 3:48am    
Question improved

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900