Group and Sum a table - 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 and Sum a table

Author

Topic

hocsql
Starting Member

3 Posts

Posted - 2011-08-08 : 14:01:32

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-08 : 15:49:19

This is not too hard to do in SQL, except for one thing - you need some way to order the rows in the table. This is required because by definition, the rows in a table are unordered. So even though to you and me it looks like the second row with the Null belongs together with the first row where itemNo is 2097, SQL Server doesn't know that. So you need some way to tell it that those two rows belong together. It could be another column which has a sequence number, or even a combination of columns that would allow you to order the data.

Without the ability to do such ordering, SQL wouldn't know how to do the grouping that you are looking for.

hocsql
Starting Member

3 Posts

Posted - 2011-08-08 : 17:21:04

Thanks for your quick respond!

there is another col call secondary_sort (Not PK) see below. the second row with the Null is belongs together with the first row or the second,third,fourth so on is belongs to previous row that have itemNo. example Null | adjustment | 15 | 4 and
Null | coinsurance | 20 | 5
is belong to row have itemNo 3216

a reminder I would like to group base on itemNo, itemDescr and sum by total

Again Thank you very much for your help

itemNo | itemDescr | total | secondary_sort

2097 | Therapeutic | 100 | 1
Null | adjustment | 20 | 2
3216 | Therapeutic | 300 | 3
Null | adjustment | 15 | 4
Null | coinsurance | 20 | 5
2097 | Therapeutic | 300 | 6
Null | adjustment | 30 | 7
3216 | Therapeutic | 60 | 8
Null | adjustment | 10 | 9
Null | coinsurance | 20 | 10
5612 | Therapeutic | 60 | 11
Null | adjustment | 10 | 12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 10:31:25

whats order by which you determine the value of secondary_sort. please keep in mind there's no concept of order in a sql table unless you specify order using ORDER BY.

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

hocsql
Starting Member

3 Posts

Posted - 2011-08-10 : 11:40:42

Thanks a lot for your fee back. Is possible that we can do?: As result add another column call "itemNo2" and get previous "itemNo" which (2097) value put in the next row of previous "itemNo" with _1 which is (2097_1)

Here a result I would like to have:

itemNo | itemNo2 | itemDescr | total | secondary_sort

2097 | 2097 | Therapeutic | 100 | 1
Null | 2097_1 | adjustment | 20 | 2
3216 | 3216 | Therapeutic | 300 | 3
Null | 3261_1 | adjustment | 15 | 4
Null | 3261_2 | coinsurance | 20 | 5
2097 | 2097 | Therapeutic | 300 | 6
Null | 2097_1 | adjustment | 30 | 7
3216 | 3216 | Therapeutic | 60 | 8
Null | 3216_1 | adjustment | 10 | 9
Null | 3216_2 | coinsurance | 20 | 10

Again, thank you very much for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources