CODEWITHSUNDEEP
sqlinformix
Vodnik
Vodnik

Reputation: 129

Please help to build SQL SELECT request with GROUP BY

I have following tables:

callrecord table
callid, calltypekey, requeuecount
123     1            -1
123     2            0
123     3            1
234     1            0
234     3            1
435     3            0
435     1            1
567     1            -1
678     1            0

calltypes table
calltypekey, calltypename
1            name1
2            name2
3            name3

Following SQL request works fine:

SELECT callid, min(requeuecount) as minreq, max(requeuecount) as maxreq
FROM callrecord 
GROUP BY callid
HAVING count(callid) > 1

and produce the following result:

callid, minreq, maxreq 
123     -1      1
234     0       1
435     0       1

I would like to add calltypekey value corresponding to min(requeuecount) record, i.e. result should be the following:

callid, minreq, maxreq, minCTK, maxCTK 
123     -1      1       1       3
234     0       1       1       3
435     0       1       3       1

or even with joined names:

callid, minreq, maxreq, minname, maxname 
123     -1      1       name1    name3
234     0       1       name1    name3
435     0       1       name3    name1

Is this possible?

Upvotes: 1

Views: 65

Answers (2)

Fahmi
Fahmi

Reputation: 37493

You can try below -

select A.callid,minreq,maxreq,C.calltypekey as minCTK,C1.calltypekey as maxCTK  from 
(
  SELECT callid, min(requeuecount) as minreq, max(requeuecount) as maxreq
    FROM callrecord 
    GROUP BY callid
    HAVING count(callid) > 1
)A left join callrecord C on A.minreq=C.requeuecount
   left join callrecord C1 on A.maxreq=C1.requeuecount

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13026

Please give this a try.

select t1.callid, t1.minreq, t1.maxreq, t2.Name as minname, t3.Name as maxname from (
SELECT callid, min(requeuecount) as minreq, max(requeuecount) as maxreq
FROM callrecord 
GROUP BY callid
HAVING count(callid) > 1) as t1
left join 
    (select c.callid, ct.Name, c.requeuecount from callrecord c
        inner join calltypes ct on ct.calltypekey = c.calltypekey)  t2 on t2.callid = t1.callid and t2.requeuecount = t1.minreq
left join 
    (select c.callid, ct.Name, c.requeuecount from callrecord c
        inner join calltypes ct on ct.calltypekey = c.calltypekey)  t3 on t3.callid = t1.callid and t3.requeuecount = t1.maxreq

Upvotes: 1

Related Questions