Error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Question

I have a query that I have created, when I run it I get these errors:

Msg 130, Level 15, State 1, Line 12
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 12
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 15
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

   1. select a.*
     2. from
     3. (
     4. select
     5. ltrim(rtrim(job_number)) as job,
     6. max(ltrim(rtrim(paint_code))) as paint_code,
     7. max(ltrim(rtrim(d31.alphavalue))) as shipped_day,
     8. max(COMPANY_CODE) as plant,
     9. max(ltrim(rtrim(customer_name))) as customer,
     10. max(date_promised) as promise_dt,
     11. max(date_requested) as requested_dt,
     12. max(case when coalesce((SELECT MAX(date_shipped_sm) FROM jobscopedb.ppshipm WHERE ((carrier like '%KIT%') and ltrim(rtrim(substring(job_shipment_sm,1, charindex(' ', job_shipment_sm)))) = ltrim(rtrim(job_number_jbmstr)))), 0) = 0 then 99999999
     13. else
     14. (SELECT MAX(date_shipped) FROM JobscopeMeyer.dbo.ppshipm WHERE ((carrier NOT like '%KIT%') and ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) = ltrim(rtrim(job_number))))
     15. end) as ship_dt,
     16. max(price_curr) as sales_amount
     17. from JobscopeMeyer.dbo.ipjobm
     18. inner join JobscopeMeyer.dbo.ppjobd on
     19. job_number = release
     20. left outer join (
     21. select
     22. max(date_shipped) as ship_dt,
     23. ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) as job
     24. from
     25. JobscopeMeyer.dbo.ppshipm
     26. where
     27. carrier like '%KIT%'
     28. group by
     29. ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment))))
     30. ) sm on
     31. sm.job = ltrim(rtrim(job_number))
     32. left outer join JobscopeMeyer.dbo.AJ_D31 d31 on
     33. ltrim(rtrim(job_number)) = ltrim(rtrim(d31.SEARCHKEY))
     34. where company_code  = '06'
     35. 
     36. group by
     37. JOB_NUMBER
     38. --            order by
     39. --              job_number
     40. ) as a
     41. where a.promise_dt between '20210201' and '20210228'
     42. and a.ship_dt = '99999999'
     43. and a.shipped_day != ' '
     44. and a.requested_dt != '0'
     45. 
     46. order by ship_dt, job

Answer 1

Hi @NBoddie ，

Welcome to the microsoft TSQL Q&A forum!

SQL Server does not support the parameter of the aggregate function to be a subquery or to include an aggregate function.In the following code, max is included in the parameter of the max function：

   max(case when coalesce((SELECT MAX(date_shipped_sm) FROM jobscopedb.ppshipm WHERE ((carrier like '%KIT%') and ltrim(rtrim(substring(job_shipment_sm,1, charindex(' ', job_shipment_sm)))) = ltrim(rtrim(job_number_jbmstr)))), 0) = 0 then 99999999  
        else  
        (SELECT MAX(date_shipped) FROM JobscopeMeyer.dbo.ppshipm WHERE ((carrier NOT like '%KIT%') and ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) = ltrim(rtrim(job_number))))  
        end) as ship_dt  

To solve this problem, you can separate the max in the parameter through the derivation table:

select  
ltrim(rtrim(job_number)) as job,  
max(ltrim(rtrim(paint_code))) as paint_code,  
max(ltrim(rtrim(d31.alphavalue))) as shipped_day,  
max(COMPANY_CODE) as plant,  
max(ltrim(rtrim(customer_name))) as customer,  
max(date_promised) as promise_dt,  
max(date_requested) as requested_dt,  
max(ship_dt) as ship_dt,  
max(price_curr) as sales_amount  
from (select *,case when coalesce((SELECT MAX(date_shipped_sm) FROM jobscopedb.ppshipm WHERE ((carrier like '%KIT%') and ltrim(rtrim(substring(job_shipment_sm,1, charindex(' ', job_shipment_sm)))) = ltrim(rtrim(job_number_jbmstr)))), 0) = 0 then 99999999  
else  
(SELECT MAX(date_shipped) FROM JobscopeMeyer.dbo.ppshipm WHERE ((carrier NOT like '%KIT%') and ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) = ltrim(rtrim(job_number))))  
end) as ship_dt) from JobscopeMeyer.dbo.ipjobm) JobscopeMeyer.dbo.ipjobm  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Please try:

    select a.*
    from
    (select
     ltrim(rtrim(job_number)) as job,
     max(ltrim(rtrim(paint_code))) as paint_code,
     max(ltrim(rtrim(d31.alphavalue))) as shipped_day,
     max(COMPANY_CODE) as plant,
     max(ltrim(rtrim(customer_name))) as customer,
     max(date_promised) as promise_dt,
     max(date_requested) as requested_dt,
     max(ship_dt) as ship_dt,
     max(price_curr) as sales_amount
     from (select *,case when coalesce((SELECT MAX(date_shipped_sm) FROM jobscopedb.ppshipm WHERE ((carrier like '%KIT%') and ltrim(rtrim(substring(job_shipment_sm,1, charindex(' ', job_shipment_sm)))) = ltrim(rtrim(job_number_jbmstr)))), 0) = 0 then 99999999
     else
     (SELECT MAX(date_shipped) FROM JobscopeMeyer.dbo.ppshipm WHERE ((carrier NOT like '%KIT%') and ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) = ltrim(rtrim(job_number))))
     end as ship_dt from JobscopeMeyer.dbo.ipjobm) t
    inner join JobscopeMeyer.dbo.ppjobd on job_number = release

If this does not solve your problem,,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

In addition, using aliases is a good choice:

     end as ship_dt from JobscopeMeyer.dbo.ipjobm) t
    inner join JobscopeMeyer.dbo.ppjobd j on t.job_number =j.release

Regards
Echo

Answer 3

The issue I am having is how do I take ship_dt and put it in the query, since that way I had it will not work ?

max(case when coalesce((SELECT MAX(date_shipped_sm) FROM jobscopedb.ppshipm WHERE ((carrier like '%KIT%') and ltrim(rtrim(substring(job_shipment_sm,1, charindex(' ', job_shipment_sm)))) = ltrim(rtrim(job_number_jbmstr)))), 0) = 0 then 99999999
else
(SELECT MAX(date_shipped) FROM JobscopeMeyer.dbo.ppshipm WHERE ((carrier NOT like '%KIT%') and ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) = ltrim(rtrim(job_number))))
end) as ship_dt
This query below and populating all the fields I need besides the ship_dt:

(select a.*  
from   
(  
select  
                ltrim(rtrim(job_number)) as job,  
                max(ltrim(rtrim(paint_code))) as paint_code,  
                max(ltrim(rtrim(d31.ALPHAVALUE))) as shipped_day,  
                max(company_code) as plant,  
                max(ltrim(rtrim(customer_name))) as customer,  
                max(date_promised) as promise_dt,  
                max(date_requested) as requested_dt,  
				max(price_curr) as sales_amount  
  
            from JobscopeMeyer.dbo.ipjobm   
            inner join JobscopeMeyer.dbo.ppjobd on   
                job_number = release 		 						  
            left outer join (  
                select  
                    max(date_shipped) as ship_dt,  
                    ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment)))) as job  
                from  
                    JobscopeMeyer.dbo.PPSHIPM  
                where  
                    carrier like '%KIT%'  
                group by  
                   ltrim(rtrim(substring(job_shipment,1, charindex(' ', job_shipment))))  
            ) sm on   
                                                            sm.job = ltrim(rtrim(job_number))  
            left outer join JobscopeMeyer.dbo.AJ_D31 d31 on   
                ltrim(rtrim(job_number)) = ltrim(rtrim(d31.SEARCHKEY))  
          where company_code  = '06'  
  
            group by   
                job_number  
  --          order by   
    --            job_number  
) as a  
where a.promise_dt between 20210201 and 20210231  
--and a.ship_dt = '99999999'  
and a.shipped_day != ' '  
and a.requested_dt != '0'  
)  
--order by ship_dt, job    
order by  job