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

NBoddie 1 Reputation point
2021-04-14T03:25:20.12+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-04-14T03:50:43.44+00:00

    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.

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-04-14T05:44:22.84+00:00

    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

    0 comments
  3. Naquessa Boddie 1 Reputation point
    2021-04-16T05:50:31.313+00:00

    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

    88513-image.png