?
Solved

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

Posted on 2004-12-01
6
Medium Priority
?
921 Views
Last Modified: 2012-05-05
Hi,

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),
                            t.transactiontime))
from vtransaction t
left join vparts p on t.partId = p.id
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?
0
Comment
Question by:Bryan_Webb
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:strangelynormal1
ID: 12722459
Try HAVING...

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

Accepted Solution

by:
strangelynormal1 earned 400 total points
ID: 12722560
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      = p.id
      ,(      SELECT      transactiontime      = MAX(transactiontime)
                  ,partid
                  ,parkinglotid
            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
0
 

Author Comment

by:Bryan_Webb
ID: 12727790
stranelynormal,

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,
Bryan
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:strangelynormal1
ID: 12728675
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)
               ,partid
          FROM     vtransaction     t2
          WHERE     transactiontype     = 9
          AND     parkinglotid     = @lotid
          GROUP BY partid)     t2
WHERE     t.partId     = p.id
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 question...so I'll stop my babble now.

Hope this helps!
0
 

Author Comment

by:Bryan_Webb
ID: 12728948
Strangelynormal,

I see now, thanks for the explanation.

Regards,
Bryan
0
 
LVL 4

Expert Comment

by:strangelynormal1
ID: 12729319
Cool...not a problem!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 setup several different housekeeping processes for a SQL Server.
Suggested Courses

850 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