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.
8,596 questions
{count} votes

3 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,416 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,416 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

    No 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