How do I join/group 2 or more rows that start with the same 4 characters in MS SQL

Question

0.00/5 (No votes)

See more:

Hi there,

I'm trying to create a view and to group or join two or more rows that start with the same 4 characters.

Currently, the code that I have is:

SQL

select (left(P.PartNum, 4)), sum(P.OnhandQty) as PartNum2 
from PartBin P 
group by PartNum

I'm trying to group/join them so that if there's 5 parts that start with AA11 and have a quantity of 2 each then the output would be:
Part # Qty
AA11 10
Instead of:
Part # Qty
AA11T9 2
AA11B4 2
AA1143 2
AA1173 2
AA11A5 2

What I have tried:

SQL

select left(Q.PartNum, 4), sum(Q.OnhandQty) as PartNum3
from PartBin Q
where left(Q.PartNum, 4) like left(Q.PartNum, 4)
group by PartNum

Posted 7-Mar-18 1:54am

Member 13713473

Updated 7-Mar-18 4:21am

Add a Solution

2 solutions

Solution 1

You need to use the appropriate JOIN statement.

The most commonly used are INNER JOIN, LEFT JOIN and RIGHT JOIN.

The first only returns rows that match.
The other two return all of one set, even if there's no match, but makes the match if possible. You will almost certainly need INNER JOIN.

HOW TO USE: go here SQL INNER JOIN Keyword[^] to get you started.

The "ON" part will use the LEFT(field, 4) from each.

Posted 7-Mar-18 2:10am

W∴ Balboos, GHB

Comments

Member 13713473 7-Mar-18 8:34am

As you can see of what I've tried, all the information that I need/want is from one table.
Would I still use the Inner Join?

W∴ Balboos, GHB 7-Mar-18 8:38am

You can join a table to itself. It's actually common.

I use it, for example, to take rows and convert them to columns. Actually exactly what you're doing if you think about it.

Just give each version of the table its own letter
SELECT A.x, A.y, B.y, C.y from TABLE as A
INNER JOIN TABLE as B
ON A.x=B.x
INNER JOIN TABLE AS C
ON A.x=C.x

This will give me the key (x) and the values for y as three additional columns. You probably want to name the columns, as well, so they can be told apart.

Member 13713473 7-Mar-18 10:25am

For some reason, that's not working as I thought it would. What I've done is:

Select A.Colum1, Sum(A.Colum2)
From Table1 A
Inner Join PartBin B
On Left(A.Colum1, 4) = Left(B.Colum1, 4)
Group By A.Colum1, A.Colum2
Order By A.Colum1, A.Colum2 ASC

And it's still bringing up:

Colum1 Colum2
AA11B3 2
AA11F4 2
AA113D 2
AA11F7 2
AA11S2 2

Instead of:
Colum1 Colum2
AA11 10

W∴ Balboos, GHB 7-Mar-18 10:28am

Select A.Colum1, Sum(A.Colum2) - is only looking at data from the "A" return values. The only thing your join is doing is acting as a filter to limit those that come back to those that have matches.

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)

Andrew Lyzlov · Accepted Answer · 2018-03-07T04:22:00

Solution 2

Hi,
This should work:

SELECT 
	LEFT(Q.PartNum,4) AS PartNumLeft, 
	SUM(Q.OnhandQty) AS PartNumQty
FROM PartBin AS Q
GROUP BY 
	LEFT(Q.PartNum,4)

Posted 7-Mar-18 4:22am

Andrew Lyzlov