locked
Aggregate Function in SQL subquery RRS feed

  • Question

  • Hello,

    I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.

    So in this case I am using this syntax to show the most recent quote within the project.

    SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
    FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
    WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
    

    My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.

    Let me know if this isn't clear.

    Thanks.

    • Edited by mntyguy Wednesday, January 15, 2014 7:17 PM
    Wednesday, January 15, 2014 7:17 PM

Answers

  • SInce you didnt provided DDL/DML, I guessed and derived this query

    declare @project table (ProjectID int,
    						ProjectName varchar(10))
    insert @project values (1,'Test'),(1,'Passed'),(2,'reals'),(3,'GTS'),(3,'FUNS'),(5,'houses')
    
    
    --select * from @project
    
    declare @Quote table (ProjectID int,
    					  QuoteCode varchar(10),
    					  QuoteDate date)
    insert @Quote values (1,'p','2014-01-15'),(2,'f','2014-01-01'),(2,'p','2014-01-17'),(3,'p','2013-01-01'),(3,'f','2013-10-20'),(4,'T','2012-10-11'),(5,'h','2015-01-01'),(5,'p','2014-10-23')
    						
    --select * from @Quote						
    ;with cte as (
    				select MAX(QuoteDate)as QuoteDate,qt.ProjectID  from @Quote qt
    						   where  exists (select * from @project as p where  p.ProjectID = QT.ProjectID)
    				group by qt.ProjectID
    				)
    select pt.*,cte.QuoteDate 
    from @project as pt
    left join cte on pt.ProjectID=cte.ProjectID


    Thanks, hsbal

    • Proposed as answer by Sofiya Li Thursday, January 16, 2014 7:03 AM
    • Marked as answer by Sofiya Li Thursday, January 23, 2014 7:16 AM
    Wednesday, January 15, 2014 8:57 PM

All replies

  • Try the below query

    SELECT P1.projectID,p1.QuoteDate, Q1.QuoteCode,p1.* FROM PROJECT P1 inner join (SELECT Q.ProjectID,Max(Q.QuoteDate) QD FROM QUOTE Q group by Q.ProjectID) Q1

    on Q1.ProjectID = P1.ProjectID and Q1.QD=P1.QuoteDate

    -Prashanth


    Wednesday, January 15, 2014 7:24 PM
  • SInce you didnt provided DDL/DML, I guessed and derived this query

    declare @project table (ProjectID int,
    						ProjectName varchar(10))
    insert @project values (1,'Test'),(1,'Passed'),(2,'reals'),(3,'GTS'),(3,'FUNS'),(5,'houses')
    
    
    --select * from @project
    
    declare @Quote table (ProjectID int,
    					  QuoteCode varchar(10),
    					  QuoteDate date)
    insert @Quote values (1,'p','2014-01-15'),(2,'f','2014-01-01'),(2,'p','2014-01-17'),(3,'p','2013-01-01'),(3,'f','2013-10-20'),(4,'T','2012-10-11'),(5,'h','2015-01-01'),(5,'p','2014-10-23')
    						
    --select * from @Quote						
    ;with cte as (
    				select MAX(QuoteDate)as QuoteDate,qt.ProjectID  from @Quote qt
    						   where  exists (select * from @project as p where  p.ProjectID = QT.ProjectID)
    				group by qt.ProjectID
    				)
    select pt.*,cte.QuoteDate 
    from @project as pt
    left join cte on pt.ProjectID=cte.ProjectID


    Thanks, hsbal

    • Proposed as answer by Sofiya Li Thursday, January 16, 2014 7:03 AM
    • Marked as answer by Sofiya Li Thursday, January 23, 2014 7:16 AM
    Wednesday, January 15, 2014 8:57 PM