CODEWITHSUNDEEP
sql
Yubaraj
Yubaraj

Reputation: 3988

How can I use group by clause correctly in sql

I used following query to get expected output but did not work properly.

with f1 as 
(
    select 
        case when f.eventid = 5 then f.timestamps end as starttime,
        case when f.eventid = 6 then f.timestamps end as endtime,
        f.instanceidentifier as identifier
    from 
        frarecord f 
)
select 
    starttime, endtime, identifier 
from f1

I got following output:

enter image description here

But output should come with group by identifier.

I also tried to use group by clause for identifier. Exception says me to group by starttime and endtime too.

Is there any solution?

Note: This question is closely related with How can I calculate average with condition in sql :)

Upvotes: 0

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Try using aggregation:

with f1 as (
      select (case when f.eventid=5 then f.timestamps end) as starttime,
             (case when f.eventid=6 then f.timestamps end) as endtime,
            f.instanceidentifier as identifier
      from frarecord f
     )
select min(starttime) as starttime, max(endtime) as enddtime, identifier
from f1
group by identifier;

Upvotes: 2

Related Questions