SQLTeam.com | Weblogs | Forums

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Hi, I am having an issue on my calculation subqueries with sum() and they give me the error in the subject line.
My queries is pasted below, your help will be highly appreciated.

SELECT
account.id AS accid,
account.accno,
person.fileno,
person.fileno_old AS oldfileno,

  (
     SELECT userpick.name
     FROM ikat3dbanderson.userpick
     WHERE userpick.id = person.title
  ) AS title, 

  (
     SELECT ikatpick.name
     FROM ikat3dbanderson.ikatpick
     WHERE ikatpick.id = person.gender
  ) AS gender, 

person.firstname,
person.initials,
person.lastname,
person.idno,
person.dob,
person.employer,
address.mail1,
address.mail2,
address.mail3,
address.mailpcode AS mailpcode,
address.email,
address.street1,
address.street2,
address.street3,
address.streetpcode AS streetpcode,
address.url AS website,
phone.phone1 AS WORK,
phone.phone2 AS home,
phone.phone3 AS cell,
phone.fax,
(
SELECT accgroup.name
FROM ikat3dbanderson.accgroup
WHERE accgroup.id = account.accgrp
) AS accgroup,

  (

     SELECT coalesce(sum(tranc.patportion + tranc.maportion) - sum(ISNULL(
        (
		
           SELECT sum(coalesce(allocations.patallocation, 0))
           FROM ikat3dbanderson.allocations
           WHERE allocations.totran = tranc.id
		  
        ), N'0')) - sum(ISNULL(
        (
          
           SELECT sum(coalesce(allocations.maallocation, 0))
           FROM ikat3dbanderson.allocations
           WHERE allocations.totran = tranc.id

        ), N'0')) - ISNULL(
        (
           SELECT sum(crednote.amt)
           FROM ikat3dbanderson.crednote
           WHERE crednote.account = account.id AND crednote.provider IN 
              (
                 SELECT provider.id
                 FROM ikat3dbanderson.provider
                 WHERE provider.pcentre = 1
              )
        ), N'0'), N'0')
     FROM ikat3dbanderson.transactions  AS tranc, ikat3dbanderson.person  AS patient
     WHERE 
        tranc.personid = patient.id AND 
        tranc.provid IN 
        (
           SELECT provider.id
           FROM ikat3dbanderson.provider
           WHERE provider.pcentre = 
              (
                 SELECT provider.pcentre
                 FROM ikat3dbanderson.provider
                 WHERE provider.id = 1
              )
        ) AND 
        patient.account = account.id AND 
        (ISNULL(tranc.delflag, '1899-12-31 00:00:00') < '1900-01-01 00:00:00')
  ) AS balance, 

  (
     SELECT medaid.name + N' ' + maplan.name
     FROM ikat3dbanderson.medaid, ikat3dbanderson.maplan
     WHERE medaid.id = maplan.scheme AND maplan.id = person.medaid
  ) AS medaid, 

  (
     SELECT maplan.name
     FROM ikat3dbanderson.medaid, ikat3dbanderson.maplan
     WHERE medaid.id = maplan.scheme AND maplan.id = person.medaid
  ) AS maplan, 

person.mano AS medaidno,
col_colourcode.col_id,
col_colourcode.col_description,
col_colourcode.col_colour,
col_colourcode.col_fontcolour
FROM
ikat3dbanderson.account
LEFT JOIN ikat3dbanderson.col_colourcode
ON account.col_id = col_colourcode.col_id,
ikat3dbanderson.person,
ikat3dbanderson.address,
ikat3dbanderson.phone,
ikat3dbanderson.accgroup
WHERE
account.persid = person.id AND
person.address = address.id AND
person.phone = phone.id AND
account.accgrp = accgroup.id AND
person.acc_holder_flag = 'T' AND
upper(account.accno) LIKE upper(N'%%') AND
upper(person.firstname) LIKE upper(N'%%') AND
upper(person.lastname) LIKE upper(N'%%') AND
upper(person.idno) LIKE upper(N'%%') AND
upper(person.fileno) LIKE upper(N'%%') AND
upper(phone.phone1) LIKE upper(N'%%') AND
upper(phone.phone2) LIKE upper(N'%%') AND
upper(address.street1) LIKE upper(N'%%') AND
upper(address.mail1) LIKE upper(N'%%') AND

CASE
WHEN ('F' = 'T') THEN
CASE
WHEN (upper(
(
SELECT medaid.name + N' ' + maplan.name
FROM ikat3dbanderson.medaid, ikat3dbanderson.maplan
WHERE medaid.id = maplan.scheme AND maplan.id = person.medaid
)) LIKE upper(N'%%')) THEN 1
ELSE 0
END
ELSE N'1'
END <> 0 AND

CASE
WHEN ('F' = 'T') THEN
CASE
WHEN (FORMAT(person.dob, N'%d/%m/%Y') = '') THEN 1
ELSE 0
END
ELSE N'1'
END <> 0 AND
coalesce(account.col_id, -1) LIKE N'%' AND
account.active = 'T'
ORDER BY account.accno

It is better to break-up your query in smaller sets and join them back to get the final result. And as the error says, you cannot have an aggregate on an expression containing an aggregate i.e. in your case you are trying to do this: sum(some expression with a sum), this is not allowed. Try to take the data from your main table into a temp. table and then do your additional joins on top of that (create further temp tables if required and add indexes), break your select query into smaller sets.

1 Like

Hi, thank you for your speedy response, I will check it out and try again. Thank you