Click here to Skip to main content
14,773,088 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 22: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.
   
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
OriginalGriff 12-Jul-20 4:01am
   
Answer updated.
Hemil Gandhi 12-Jul-20 10:36am
   
I have build below mentioned query to get PAN wise data, Now I am clue less how to get name without duplication of PAN.

select customer.pan,count(LOAN.Acno) as Count,sum(LOAN.Bal) from LOAN inner join customer
on LOAN.CustNo=customer.Code where AcSts<>9 and len(customer.pan)=10 group by PAN
OriginalGriff 12-Jul-20 11:04am
   
Read what I said: "Start by writing a SELECT using the GROUP BY", "test it and check you have the right results"
Then add the JOIN - if you aren't sure how, start with your course notes on GROUP BY (through it's pretty simple) then reread the ones on JOIN.

GROUP BY can only ever return the grouping field and Aggregate results, never specific column content that isn't in the GROUP.
Stop guessing, start thinking! :laugh:
Hemil Gandhi 13-Jul-20 10:43am
   
select c.name,c.pan,count(L.Acno) as Count,sum(L.ClearBal) from LON20190629 as L inner join customer as C
on L.CustNo=c.Code inner joiN
(
SELECT pan, MIN(code) MinCustId
FROM customer
GROUP BY PAN
) tbl1
ON tbl1.PAN = C.PAN
WHERE tbl1.MinCustID = C.code AND l.AcSts<>9 group by c.PAN,c.Name
OriginalGriff 13-Jul-20 10:58am
   
Add the COUNT and SUM to the GROUP BY if they are in the same table - your question implies they are, but that code shows three tables instead of the one you originally had.
For the one table, I did this:
SELECT g.PAN, 
       g.Count, 
       m.Name, 
       g.Balance 
FROM MyTable m
JOIN (SELECT PAN, 
             SUM(Balance) AS Balance, 
             MIN([Cust No]) AS CustNo, 
             COUNT([Cust No]) AS Count 
      FROM MyTable
      GROUP BY PAN) g
ON g.CustNo = m.[Cust No];
Hemil Gandhi 13-Jul-20 12:58pm
   
I have two tables loan & customer, loan table is having customer number & balance where as customer table is having customer number,name & PAN.
OriginalGriff 14-Jul-20 2:30am
   
It's a really good idea to give people the right information when you first post a question - a single table is different to a multiple table and that can change the answer.
This is a profession that needs accuracy, so providing "sort of like this" information doesn't really help anyone.
Hemil Gandhi 18-Jul-20 13:21pm
   
Noted...!!! Finally I have made this query which skips the records of duplicate pan.

select c.name,c.pan,count(L.Acno) as Count,sum(L.ClearBal) as bal from LON20190629 as L inner join customer as C
on L.CustNo=c.Code inner joiN
(
SELECT pan, MIN(code) MinPoint
FROM customer
where len(pan)>6
GROUP BY PAN
) tbl1
ON tbl1.PAN = C.PAN
WHERE tbl1.MinPoint = C.code AND L.AcSts<>9 and l.ClearBal<0 group by c.PAN,c.Name order by bal desc

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