Combining Lines - 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

General SQL Server Forums

New to SQL Server Programming

Combining Lines

Author

Topic

Tototo1
Starting Member

22 Posts

Posted - 2011-12-27 : 16:11:33

I am writing a very simple query but I am trying to combine lines with a common field. My quesry is:

select lmatter, ltradat, linvoice, Amount from ledger
where llcode = 'WOFF-G' and lzero = 'Y'
order by lmatter,ltradat, linvoice

This will generate all of the information I need, however I would like to combine all like values of linvoice to only show on one line with the amounts added together. Currently I receive something like this when I run the program:

lmatter ltradat linvoice Amount
00001 12/12/2011 123 $80
00002 12/13/2011 125 $25
00002 12/13/2011 125 $60
00002 12/13/2011 125 $90
00003 12/14/2011 198 $598

I would like it to combine rows 2-4 to read:
lmatter ltradat linvoice Amount
00001 12/12/2011 123 $80
00002 12/13/2011 125 $175
00003 12/14/2011 198 $598

Any ideas?

Thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-27 : 16:16:52

You can use GROUP BY and SUM to achieve this.

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

Subscribe to my blog

Tototo1
Starting Member

22 Posts

Posted - 2011-12-27 : 16:33:56

I get an error when trying to do a GROUP BY function that:

Column 'ledger.lmatter' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I can only get this to work if I only query on linvoice, but I need the other information. Does this make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-27 : 17:04:36

Show us what you tried and a better data example. You'll likely need to use a derived table or ROW_NUMBER() function.

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

Subscribe to my blog

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-27 : 21:44:54

Try this...

GO

Insert into #VPTable values ('00001','12/12/2011',123,80)
Insert into #VPTable values ('00002','12/13/2011',125,25)
Insert into #VPTable values ('00002','12/13/2011',125,60)
Insert into #VPTable values ('00002','12/13/2011',125,90)
Insert into #VPTable values ('00003','12/14/2011',198,598)

SELECT l.lmatter, l.ltradat, l.linvoice, SUM(l.Amount) as Amount from #VPTable l
GROUP BY l.lmatter, l.ltradat, l.linvoice

Result:
lmatter ltradat linvoice Amount
-----------------------------------------------
00001 2011-12-12 00:00:00.000 123 80
00002 2011-12-13 00:00:00.000 125 175
00003 2011-12-14 00:00:00.000 198 598

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources