Group similar rows - SQL Server Forums

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)

Group similar rows

Author

Topic

DavidSpackman
Starting Member

4 Posts

Posted - 2010-08-24 : 04:03:12

I have a table similar to to one like this below.

I would like to display the sum of the mrp_qty rows grouped against the mrp_type column.

However I would like to be able to group mrp_type rows with similar names.
For example below I would like to combine the rows with mrp_type values SUPPLYP and SUPPLY
(It does not need to by dynamic, the use case below is enough).


mrp_dataset	mrp_part	mrp_nbr		mrp_line	mrp_qty		mrp_type
wo_mstr		946		0320		6793		5.0000000000	SUPPLYP
wo_mstr		946		0324		6796		16.0000000000	SUPPLYP
wo_mstr		946		1024		6704		6.0000000000	SUPPLY
wod_det		946		0507		6847		8.0000000000	DEMAND
wod_det		946		0515		6865		2.0000000000	DEMAND

I needed to output the sum of the MRP_QTY column, grouped by the MRP_TYPE, so I used the following code


SELECT 
	mrp_type
	,SUM(mrp_qty) as qty
FROM 
	(*My table name*)
GROUP BY
	mrp_type


MRP_TYPE	Qty
SUPPLYP		21.00
SUPPY		6.00
DEMAND		10.00

Is it possible to further group the results like this?


MRP_TYPE	Qty
SUPPY Comb.	27.00
DEMAND		10.00

Thanks very much for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 05:02:30

[code]SELECT
left(mrp_type,6) as mrp_type,
,SUM(mrp_qty) as qty
FROM
(*My table name*)
GROUP BY
left(mrp_type,6) [/code]

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:46:04

if names to combine always cannot be grouped like what Madhi shows then you might need to create another mapping table to determine what all you need consider as one.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DavidSpackman
Starting Member

4 Posts

Posted - 2010-08-24 : 19:20:15

Thank you very much Madhivanan, it worked well, and suits what I need the query to do.

However visakh16, I am curious on how I could create another mapping table to group the results too, for reference :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-25 : 05:02:00

quote:
Originally posted by DavidSpackman

Thank you very much Madhivanan, it worked well, and suits what I need the query to do.

However visakh16, I am curious on how I could create another mapping table to group the results too, for reference :)

You are welcome

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:44:13

quote:
Originally posted by DavidSpackman

Thank you very much Madhivanan, it worked well, and suits what I need the query to do.

However visakh16, I am curious on how I could create another mapping table to group the results too, for reference :)

it will simply have two columns like

Actual_mrp_type | Mapped_mrp_type

then store it like

SUPPLYP SUPPLY
SUPPLY SUPPLY
SUPPLY & CO SUPPLY
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources