Simple Grouping confusion - 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 2008 Forums

Transact-SQL (2008)

Simple Grouping confusion

Author

Topic

Muzaffar557
Starting Member

11 Posts

Posted - 2012-01-02 : 03:37:39

i have data as:
jobNo Division CostCde CostDesc Hrs
----- --------- ------- ---------------------- ----
0155 NO RECORD B015 GENERAL MANPOWER 20
0155 PRODUCTION B001 PRODUCTION MASON 14112
0155 ERECTION B018 REQUIREMENTS SITE 27
0155 QUALITY CONTROL B014 QUALITY CONTROL HELPER 1644
0155 QUALITY CONTROL B015 GENERAL MANPOWER 2975
0155 QUALITY CONTROL B013 QUALITY CONTROL MASON 607
0155 PRODUCTION B019 GENERAL REQ 20146
0155 LOGISTIC B007 STACKING & LOADING 2142
0155 GARAGE B015 GENERAL MANPOWER 457
0155 ERECTION B009 ERECTION WORKS 3465
------------------------------------------------------------
I need to combine some cost codes like,
(B015=B001=B018) and (B014=B015),so on.
Then group the data and summing the last column Hrs.
What i am doing now is creating a temp table updating the costCodes then grouping it. But i think there should be another and better way.
The challenge is to group a data with considering some column data rows as one row then grouping.
Please reply!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-02 : 08:12:18

Create a new GroupsTable which would have two columns: The CostCode and a Grouping Code. So in your example, the data in the table would be:

CostCode  GroupingCode
B015      B015
B001      B015
B018      B015

Then join with this new table and group by the GroupingCode.

I wrote this much, and then only saw that your example is:"(B015=B001=B018) and (B014=B015)". Are these two distinct groups? If B015 is grouped with B001 and B018, and then B014 is grouped with B015, which group does a row with GroupCode = B015 belong to?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-02 : 12:03:07

i think Sunita's solution should be way to go for you. one slight difference would be as per your sample data the grouping should be like


GroupCode              CostCode
----------------------------------------
Group A                 B015
Group A                 B001
Group A                 B018
Group B                 B014
Group B                 B015

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

Muzaffar557
Starting Member

11 Posts

Posted - 2012-01-03 : 02:53:04

quote:
Originally posted by sunitabeck

Create a new GroupsTable which would have two columns: The CostCode and a Grouping Code. So in your example, the data in the table would be:
CostCode GroupingCode
B015 B015
B001 B015
B018 B015
Then join with this new table and group by the GroupingCode.

I wrote this much, and then only saw that your example is:"(B015=B001=B018) and (B014=B015)". Are these two distinct groups? If B015 is grouped with B001 and B018, and then B014 is grouped with B015, which group does a row with GroupCode = B015 belong to?

Thanks Sunita for reply , my comment was just an example what you suggested currently i am doing in similar way as u suggested , instead of creating new grouping column i was creating a new temp table then updating the existing column, but Adding a grouping column is way better.
Anything much more better can we do?

Muzaffar557
Starting Member

11 Posts

Posted - 2012-01-03 : 02:54:44

quote:
Originally posted by visakh16

i think Sunita's solution should be way to go for you. one slight difference would be as per your sample data the grouping should be like

GroupCode CostCode
----------------------------------------
Group A B015
Group A B001
Group A B018
Group B B014
Group B B015

Thanks,
Yes i agree there should be a grouping code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 06:30:30

Ok...That would scalabale in future also when you add new groups

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources