Skip to content
Advertisement

Use group by in select from nested queries

I want to run a query similar to the following

SELECT t.ticketid, 
       Min(oo.incidentid). max(t.createddate) 
FROM   (SELECT ticketid, 
               NAME, 
               createddate 
        FROM   ticket) t 
       INNER JOIN (SELECT incidentid, 
                          created, 
                          date, 
                          NAME 
                   FROM   oops) oo 
               ON t.ticketid = oo.incidentid 

Now, when I run this query it tells me that for example, ticket id needs to be contained in either an aggregate function or the GROUP BY clause. However, when I add it at the and of the query, I still get

ticketid column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What anyone tells me what I am doing wrong?

Advertisement

Answer

From what I can test this code :

SELECT t.ticketid, 
       Min(oo.incidentid) min_c,
       max(t.createddate) max_c
FROM   (SELECT ticketid, 
               NAME, 
               createddate 
        FROM   ticket) t 
       INNER JOIN (SELECT incidentid, 
                          created, 
                          date, 
                          NAME 
                   FROM   oops) oo 
               ON t.ticketid = oo.incidentid 
               group by t.ticketid

is OK. Check it please. Thanks! Here is the DEMO

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement