Summing specific 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

General SQL Server Forums

New to SQL Server Programming

Summing specific rows?

Author

Topic

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 10:43:50

Hello, my problem is simple but I simply don't understand how to fix it.

Let's say I have two rows in my query that display the same information, such as below. The problem I have is that these are not repeats, but separate accounts.

rent march '05 $100
parking march '05 $90
parking march '05 $90
op costs march '05 $75

This is a problem because I want to display them in the query together, as below.

rent march '05 $100
parking march '05 $180
op costs march '05 $75

Any ideas how?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 10:53:12

select distinct columns from your_table

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 10:53:55

Do you want the output as

parking march '05 $90

parking march '05 $180

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:02:22

quote:
Originally posted by vijayisonly

Do you want the output as
parking march '05 $90
or
parking march '05 $180

I want the output as parking march '05 $180, without removing the rent and op cost rows

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:03:28

my bad i guess i wrote that i want $90 anyways haha

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 11:03:41

[code]select col1,datecol,sum(amount)
from tableA
group by col1,datecol[/code]

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-08 : 11:05:53

If you want to sum up the totals use Group By Columns

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:11:45

quote:
Originally posted by vijayisonly

select col1,datecol,sum(amount)
from tableA
group by col1,datecol

it won't let me GROUP BY col1, aka the column with the description

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 11:13:24

Why? What error are you receiving. Pls show us the table structure too.

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:14:54

quote:
Originally posted by vijayisonly

Why? What error are you receiving. Pls show us the table structure too.

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 11:16:48

it means, you have not included 'ARDetail.Memo' in the GROUP BY clause.

Post the actual query that you tried...along with the table structure.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 11:17:13

Have you seen my first reply?
Post the code you used

Madhivanan

Failing to plan is Planning to fail

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:20:13

Sorry, this is the error I get when Memo is included:

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Here's the original query:

Select
h.InvNo as Reference, h.Invoice as Doc, d.PostingDate as Date,
d.Memo as Description, d.Amount as Amount, d.Tax as Tax,
d.Amount as Payment, i.Amount as Balance, d.Deposit

From
ARHeader h
join ARDetail d on h.Voucher = d.Voucher and h.CompCode = d.CompCode
join ARItem i on d.[LineNo] = i.[LineNo] and d.Voucher = i.Voucher

Where
i.CompCode='Will' and h.AID=475568982 and d.TransNo=0

here's the one I tried after your suggestion that resulted in the error:

Select
h.InvNo as Reference, h.Invoice as Doc, d.PostingDate as Date,
d.Memo as Description, sum(d.Amount) as Amount, d.Tax as Tax,
d.Amount as Payment, i.Amount as Balance, d.Deposit

From
ARHeader h
join ARDetail d on h.Voucher = d.Voucher and h.CompCode = d.CompCode
join ARItem i on d.[LineNo] = i.[LineNo] and d.Voucher = i.Voucher

Where
i.CompCode='Will' and h.AID=475568982 and d.TransNo=0

Group By
d.Memo, h.InvNo, h.Invoice, d.PostingDate, d.Tax, d.Amount, i.Amount, d.Deposit

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-08 : 11:20:22

Is col1 a type of TEXT? If so, you can't group on it. If it's varchar, then make sure that whatever is in your select list that you aren't aggregating appears in you group by clause.

Jim

Everyday I learn something that somebody else already knew

hardwood
Starting Member

19 Posts

Posted - 2010-06-08 : 11:20:54

quote:
Originally posted by jimf

Is col1 a type of TEXT? If so, you can't group on it. If it's varchar, then make sure that whatever is in your select list that you aren't aggregating appears in you group by clause.

Jim

Everyday I learn something that somebody else already knew

yeah its a type of text, so am i effed?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-08 : 11:35:17

you can convert it to varchar(max) and group on it that way. But if that field is just whatever somebody felt like typing in, then, yes, you're effed

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources