T-SQL stored procedure - "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Posted on 2004-12-01
Last Modified: 2012-05-05

I am writing a query to return an resultset as follows:
select       Weldment = p.partName,
      NbrProduced = sum(p.qtyExpected),
      Batches = sum(1),
      AvgWeldTime = AVG(DATEDIFF(hh,
                            (SELECT TOP 1 transactiontime trans
                              FROM vtransaction t2
                              WHERE t2.partid = t.partid AND t2.transactiontype = 9 and t2.parkinglotid=@lotid
                              ORDER BY transactionTime DESC),
from vtransaction t
left join vparts p on t.partId =
where t.parkinglotid in (select downstreamid from vparkinglotdependencies where parkinglotid = @lotid)
      and t.transactiontime < @dateto
      and t.transactiontime > @datefrom
      and t.transactionType = 8
      and p.partname is not null
      and p.parttype = 'weldment'
group by p.partName

I want to calculate the number of parts produced and the average time between two types of transactions (transactionType 8 and 9) for each part number (p.partName). Well it doesn't like AvgWeldTime = .... and gives me the error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

How can I accomplish this with a single statement?
Question by:Bryan_Webb
    LVL 4

    Expert Comment

    Try HAVING...

    SELECT stuff
    FROM  table
    WHERE stuff = conditions
    GROUP BY stuff
    HAVING aggregated_stuff = conditions
    LVL 4

    Accepted Solution

    Sorry, I misunderstood your question...try the following:
    SELECT      Weldment      = p.partName
          ,NbrProduced      = SUM(p.qtyExpected)
          ,Batches      = COUNT(*)
          ,AvgWeldTime      = AVG(DATEDIFF(hh,t2.transactiontime,t.transactiontime))
    FROM      vtransaction            t
          INNER JOIN      vparts      p
                ON      t.partId      =
          ,(      SELECT      transactiontime      = MAX(transactiontime)
                FROM      vtransaction      t2
                WHERE      transactiontype      = 9
                AND      parkinglotid      = lotid
                GROUP BY partid
                      ,parkinglotid)      t2
    WHERE      t.parkinglotid      IN (      SELECT      downstreamid
                            FROM      vparkinglotdependencies
                            WHERE      parkinglotid      = @lotid)
    AND      t.transactiontime      < @dateto
    AND      t.transactiontime      > @datefrom
    AND      t.transactionType      = 8
    AND      p.partname      IS NOT NULL
    AND      p.parttype      = 'weldment'
    AND      t.partid      = t2.partid
    GROUP BY p.partName

    Author Comment


    Thanks for your reply. Everything worked great after I changed some things around to suit my app (I didn't really want to do an AVG, as it turns out):
    ,AvgWeldTime = SUM(CONVERT(FLOAT, DATEDIFF(hh,t2.transactiontime,t.transactiontime)))/SUM(p.qtyExpected)

    If you don't mind, could you explain what is happening inside the INNER JOIN statement? I don't understand the SELECT and how it affects the INNER JOIN.

    Thanks and regards,
    LVL 4

    Expert Comment

    Hi Bryan,

    You don't necessarily need to write the words INNER JOIN.  Some people say that the INNER JOIN helps SQL Server figure out what to do faster, but I see little difference to doing the following:

    SELECT     Weldment     = p.partName
         ,NbrProduced     = SUM(p.qtyExpected)
         ,Batches     = COUNT(*)
         ,AvgWeldTime     = SUM(CONVERT(FLOAT,DATEDIFF(hh,t2.transactiontime,t.transactiontime)))/SUM(p.qtyExpected)
    FROM     vtransaction          t
         ,vparts     p
         ,(     SELECT     transactiontime     = MAX(transactiontime)
              FROM     vtransaction     t2
              WHERE     transactiontype     = 9
              AND     parkinglotid     = @lotid
              GROUP BY partid)     t2
    WHERE     t.partId     =
    AND     t.parkinglotid     IN (     SELECT     downstreamid
                        FROM     vparkinglotdependencies
                        WHERE     parkinglotid     = @lotid)
    AND     t.transactiontime     < @dateto
    AND     t.transactiontime     > @datefrom
    AND     t.transactionType     = 8
    AND     p.partname     IS NOT NULL
    AND     p.parttype     = 'weldment'
    AND     t.partid     = t2.partid
    GROUP BY p.partName

    When you have the (SELECT...) in the FROM area, you really are using that query as a table.  I suppose you could do an INNER JOIN with that (SELECT...), but I chose not to.  The INNER JOIN and the ',Table WHERE' is mostly a preference thing in my eyes.

    I'm not sure if I answered your I'll stop my babble now.

    Hope this helps!

    Author Comment


    I see now, thanks for the explanation.

    LVL 4

    Expert Comment

    Cool...not a problem!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Moving SQl Server SBS 2003 to SQL Server 2014 27 98
    Using Case  in sql queries 17 50
    SQL Query 18 62
    SQL Select Query problems 10 36
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now