get the value and the sum of the value on one line

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

get the value and the sum of the value on one line

Author

Topic

lance1524
Starting Member

11 Posts

Posted - 2009-08-17 : 14:27:15

A customer can not use a website to download a report from sql reporting services (long story...)
But they want an export of the data in a csv exactly as they would get if from the reporting server.

When the user exports the data they get fields like the following:
txtfield1 | Amount_1 | CheckDate | txtbox17
Vendor:XX | 20.5 | 7/20/09 | 9.5
vendor:XX | 20.5 | 7/20/09 | 10.0
Vendor:XX | 20.5 | 7/20/09 | 1.0

I have something like this:
SELECT 'Vendor:XX' as txtField1,
SUM (Amount), CheckDate, Amount
from ...
group by CheckDate

So i want the actual amount and the summed amount on the same line.
I dont have a problem doing it in a report, but i don't know how to do it in sql.
i get "...invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

and if i put the amount in the group by clause, then sum(amount) and amount are all the same.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 14:28:25

Where is the question?

N 56°04'39.26"
E 12°55'05.63"

lance1524
Starting Member

11 Posts

Posted - 2009-08-17 : 14:37:07

I hit enter before i was done. but I did go back and edit the post.
sorry

quote:
Originally posted by Peso

Where is the question?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 14:54:54

[code]SELECT txtField1,
CheckDate,
txtBox17,
SUM(txtBox17) OVER (PARTITION BY txtField1) AS Amount_1
FROM Table1[/code]

N 56°04'39.26"
E 12°55'05.63"

lance1524
Starting Member

11 Posts

Posted - 2009-08-18 : 07:55:37

When i add
SUM(amount) OVER(PARTITION BY CheckDate)as amount_1,
i get
Incorrect syntax near the keyword 'OVER'.
is there something wrong with the statement?

here is the whole statement

SELECT 'Vendor ID: 010042740' as textbox35,
'Total for Vendor' as textbox37,
@Total as amount_total,
CONVERT (VARCHAR(10), CheckDate,101)as CheckDate,
checknumber as checknumber_1,
SUM(amount) OVER(PARTITION BY CheckDate)as amount_1,
amount,
PONumber,
invoicenumber as invoiceNum,
VetName as textbox20,
SvcDate
FROM vwPayments2009
where vendorid = @VendorID
and (CheckDate >='8/01/09' and checkDate < '9/1/09')
group by checkDate,
checknumber,
amount,
PONumber,
invoicenumber,
VetName,
SvcDate
order by CheckDate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 08:10:31

1- You need SQL Server 2005 or later for this to work.
2- Compatibility Level must be set to 90 or higher.
3- Why did you add GROUP BY to the suggestion?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources