Grouping records by column value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping records by column value

Author

Topic

fezzik
Starting Member

7 Posts

Posted - 2007-10-03 : 16:52:14

I have table with 3 columns:

ITEM, varchar(254)
QTY_IN, int(4)
QTY_OUT, int(4)

ITEM's have distinct names but also have associations a secondary item based on the name. For example I have 2 items: FG001 and FG001G. I know these items are associated because they both include root name 'FG001'. 1 item will always be the root name and the other item will have a 'G' added to the end of the root name. I'm looking to create a view that would group items in to 1 record based on their root name. There are approximately 200+ root names atm.

Any assistance is appreciated. Please let me know if more information is needed.
~Fezzik

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-03 : 16:56:50

this is a really, really bad database design.

Anyway, we need a lot more info and some sample data. Is the root always 5 characters? What happens if we get

FG001
FG001G
FG001GA

Should that be two different groups, or one group, or can it not happen?

Before anyone can write any SQL code for you, you need to explain in clear, precise terms exactly what the rules are. If you aren't sure, that is the first step before writing any SQL.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-03 : 17:54:34

I'm not 100% sure what you are after as we have no sample data and expected output. But, here is something that might work...

DECLARE @Sample TABLE (Item VARCHAR(254), Qty_In INT, Qty_Out INT)

INSERT @Sample
SELECT 'FG001', 10, 5
UNION ALL SELECT 'FG001G', 17, 12
UNION ALL SELECT 'FG002', 34, 9
UNION ALL SELECT 'FG002G', 23, 19
UNION ALL SELECT 'FG003', 1, 0
UNION ALL SELECT 'FG003G', 78, 41


-- Method One (probably faster)
SELECT
	Item,
	(SELECT SUM(Qty_In) FROM @Sample AS T WHERE T.Item LIKE S.Item + '%') AS InCount,
	(SELECT SUM(Qty_Out) FROM @Sample AS T WHERE T.Item LIKE S.Item + '%') AS OutCount
FROM
	@Sample AS S
WHERE
	LEN(Item) = 5

-- Method Two
SELECT
	LEFT(A.Item, 5),
	SUM(A.Qty_In) AS InCount,
	SUM(A.Qty_Out) AS OutCount
FROM
	@Sample AS A
GROUP BY
	LEFT(A.Item, 5)

-- Method Three
SELECT
	A.Item,
	SUM(B.Qty_In) AS InCount,
	SUM(B.Qty_Out) AS OutCount
FROM
	@Sample AS A
INNER JOIN
	@Sample AS B
	ON B.Item LIKE  A.Item + '%'
WHERE
	LEN(A.Item) = 5
GROUP BY
	A.Item

EDIT: You posted just after me, so these will not work for you.

fezzik
Starting Member

7 Posts

Posted - 2007-10-03 : 17:56:12

Thanks for responding Jeff.

I don't have control over the database design. It is maintained by a 3rd party company. I have linked the database in SQL 2005 and I'm looking to create the view I mentioned which will be used in a Crystal Report.

Is the root always 5 characters?
No, the root can be any length.

FG001
FG001G
FG001GA

Should that be two different groups, or one group, or can it not happen?
Would be 1 group but not include FG001GA. A group should only include the root and 1 other item with the exact name of the root + G. Root names will never end in G. Some roots will not have an associated item ending with G.

Please let me know if you need more information.

~Fezzik

fezzik
Starting Member

7 Posts

Posted - 2007-10-03 : 18:01:35

Sample data:
ITEM, QTY_IN, QTY_OUT

FG001, 3, 20
FG001G, 12, 13
HF00EF, 30, 12
HF00EFG, 76, 23
DE001, 7, 8
FA001, 6, 7
FA001G, 45, 76

Sample Output:
ITEM, I_QTY_IN, I_QTY_OUT, G_QTY_IN, G_QTY_OUT

FG001, 3, 20, 12, 13
HF00EF, 30, 12, 76, 23
DE001, 7, 8, 0, 0
FA001, 6, 7, 45, 76

fezzik
Starting Member

7 Posts

Posted - 2007-10-03 : 18:16:51

SELECT ITEM, QTY_IN, QTY_OUT,
(SELECT QTY_IN FROM TABLE AS B WHERE B.ITEM LIKE A.ITEM + 'G') AS G_QTY_IN,
(SELECT QTY_OUT FROM TABLE AS B WHERE B.ITEM LIKE A.ITEM + 'G') AS G_QTY_OUT
FROM TABLE AS A WHERE A.ITEM NOT LIKE '%G'

This SQL appears to give me the results I'm looking for. Anyone notice any issues?

Thanks for responding Lamprey!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-03 : 18:30:24

Yes. If either of your in-column selects (I forget what they are called) produce more than one value, you will get an error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

fezzik
Starting Member

7 Posts

Posted - 2007-10-03 : 18:39:23

In-column selects should never produce more than 1 value but then again the database is in the hands of another party. /fingers crossed.

Thanks to everyone that responded!

-Fezzik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 02:28:20

[code]-- Prepare sample data
DECLARE @Sample TABLE (Item VARCHAR(30), qIn INT, qOut INT)

INSERT @Sample
SELECT 'FG001', 10, 5 UNION ALL
SELECT 'FG001G', 17, 12 UNION ALL
SELECT 'FG002', 34, 9 UNION ALL
SELECT 'FG002G', 23, 19 UNION ALL
SELECT 'FG003', 1, 0 UNION ALL
SELECT 'FG003G', 78, 41

-- Show the expected output
SELECT CASE RIGHT(Item, 1)
WHEN 'G' THEN LEFT(Item, LEN(Item) - 1)
ELSE Item
END AS Item,
SUM(CASE RIGHT(Item, 1)
WHEN 'G' THEN 0
ELSE qIn
END) AS iIn,
SUM(CASE RIGHT(Item, 1)
WHEN 'G' THEN 0
ELSE qOut
END) AS iOut,
SUM(CASE RIGHT(Item, 1)
WHEN 'G' THEN qIn
ELSE 0
END) AS qIn,
SUM(CASE RIGHT(Item, 1)
WHEN 'G' THEN qOut
ELSE 0
END) AS qOut
FROM @Sample
GROUP BY CASE RIGHT(Item, 1)
WHEN 'G' THEN LEFT(Item, LEN(Item) - 1)
ELSE Item
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources