error aggregate function please help

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

error aggregate function please help

Author

Topic

samlopez06
Starting Member

4 Posts

Posted - 2014-10-28 : 23:41:01

error: cannot perform an aggregate function onan expression containing
an aggregate or subquery'
hi to all,can you please help me to fix this error.:(. THanks

SELECT SUM(transaction_s.amount) AS Total_Thread, SUM(transaction_s.amount *
(SELECT amount
FROM purchases
WHERE (id = 1))) AS Commision
FROM transaction_s INNER JOIN
client ON transaction_s.client_id = client.id INNER JOIN
employees ON transaction_s.therapist = employees.id INNER JOIN
services ON transaction_s.service_id = services.id INNER JOIN
category AS cat ON services.categoryid = cat.id
WHERE (cat.id = '14')

sam

AASC
Starting Member

24 Posts

Posted - 2014-10-29 : 02:07:46

@samlopez06 try below technique as Table 'amount' do not have any join condition with other tables, and seems a constant value

DECLARE @Commision INT

SET @Commision = ( SELECT amount
FROM purchases
WHERE ( id = 1 )
)

SELECT SUM(transaction_s.amount) AS Total_Thread,
SUM(transaction_s.amount * ( @Commision )) AS Commision
FROM transaction_s
INNER JOIN client ON transaction_s.client_id = client.id
INNER JOIN employees ON transaction_s.therapist = employees.id
INNER JOIN services ON transaction_s.service_id = services.id
INNER JOIN category AS cat ON services.categoryid = cat.id
WHERE ( cat.id = '14' )

samlopez06
Starting Member

4 Posts

Posted - 2014-10-29 : 02:55:27

hi AASC.thank you for your response.
This fix the error but the value of commision is NULL.

sam

samlopez06
Starting Member

4 Posts

Posted - 2014-10-29 : 03:10:29

HI sir thanks for the help.I got int now.I change INT to float so that i can compute the total amount by decimal.thanks

sam

AASC
Starting Member

24 Posts

Posted - 2014-10-29 : 07:00:14

@samlopez06 actually this should be

DECLARE @Commision numeric(15,2)

SET @Commision = ( SELECT isnull(amount,0.00)
FROM purchases
WHERE ( id = 1 )
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-29 : 13:40:20

[code]SELECT SUM(t.Amount) AS Total_Thread,
ISNULL(p.Amount, 0) * SUM(t.Amount) AS Commision
FROM dbo.Transaction_s AS t
INNER JOIN dbo.Client AS c ON c.ID = t.Client_ID
INNER JOIN dbo.Employees AS e ON e.ID = t.Therapist
INNER JOIN dbo.[Services] AS s ON s.ID = t.Service_ID
INNER JOIN dbo.Category AS q ON q.ID = s.CategoryID
LEFT JOIN dbo.Purchases AS p ON p.ID = 1
WHERE q.ID = 14;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources