Sql query (group by)

Question

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 11-Jul-20 18:25pm

Hemil Gandhi

Updated 11-Jul-20 21:48pm

v4

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2020-07-11T19:50:00

Solution 1

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.

Posted 11-Jul-20 19:50pm

OriginalGriff

Updated 11-Jul-20 23:26pm

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