adding aggregate functions in a select clause

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 2000 Forums

SQL Server Development (2000)

adding aggregate functions in a select clause

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-08-21 : 16:07:42

I have an existing SP that selects several columns (varchar, int, bit, datetime data types), but now I want to add some aggregate functions (like SUM) in the select clause. I get the error "...is not valid in the list because it is not contained in an aggregate function". It also does not allow me to add a bit data type column as part of the group by. What is the trick to doing this? I have to do this to a lot of existing SPs, so learning the prinicple here would help me a lot. One method I've been using is to add another select query to the existing select SP and then call a multiple recordset in my asp page to reference the two select result sets.

SELECT T.lngIndex, A.txtName, T.txtAccountNum, T.dteCycleDate, T.dteTransacDate, T.txtMerchantName,
T.curForeignAmnt, T.curTransacAmnt, T.txtMerchantCity,
T.txtMerchantProv, T.txtMemoFlag, T.txtCostCenter, T.curGst, T.curPst,
T.blnForeignEx, T.curNetAmnt, T.txtComments, P.txtDescription,
P.dblQuantity, P.txtUnitOfMeasure, P.curUnitCost, P.curUnitCost * P.dblQuantity as [Total Cost],
SUM (curTransacAmnt) -- not allowed
FROM dbo.Transactions T
INNER JOIN dbo.Account A ON T.txtAccountNum = A.txtAccountNum
LEFT OUTER JOIN dbo.L3Purchases P on T.lngIndex = P.lngIndex
WHERE T.txtAccountNum IN
(
SELECT DISTINCT A.txtAccountNum
FROM dbo.ApprovalGroup As AG
INNER JOIN dbo.UserGroups AS UG
ON AG.txtInternetNum = UG.txtInternetNum
AND AG.intViewGroup = UG.lngGroupNum
INNER JOIN dbo.Account AS A ON UG.txtAccountNum = A.txtAccountNum
AND UG.txtInternetNum = A.txtInternetCompNum
WHERE (AG.txtUserID = 'Admin1')
AND (AG.txtInternetNum = '9531')
)
AND (T.dteCycleDate = '7/25/2003')
ORDER BY T.txtAccountNum, T.dteTransacDate, T.lngIndex

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-21 : 16:58:51

You can add a bit column to a group by by converting it

select bitcol = convert(int,bitcol), ...
from tbl
group by convert(int,bitcol)

as for adding an aggreagate to a query - what do you want the aggregate to hold. The same value repeated for each member of a group? You can do this via a subquery

select a,b,agg = (select sum(c) from tbl t2 where t2.a = tbl.a and t2.b = tbl.b)
from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lozitskiy
Starting Member

28 Posts

Posted - 2003-08-21 : 17:00:03

What about subquery?

select .....,(select sum(...) from ... where ...=...)
from ....
join .....

-------------
MCP MSSQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources