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' )
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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 )
)
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -