sub queries

Database
Enthusiast

sub queries

Can anyone please teach me how to perform this task.

I would like to have the user_id, the number of ticket associated this this user_id and the creation date of each ticket(CRTN_DTTM). Can I do this with 1 query? or do I have to do a subquery?

Here's the code:

SELECT USERID, CRTN_DTTM, COUNT(USERID) AS TICKET_GENERATED
FROM VEMEDY_SYM_TICKET
WHERE CRTN_DTTM>='2008-07-21 00:00:00' AND CRTN_DTTM<='2009-01-20 00:00:00' AND
DESK = 'TE' AND SYMP_SERVICE NOT IN ('DIAL','INTERNET PORTABLE', 'INTERNET ST') AND
ORIGIN = 'IC' AND
DESK_ALIAS = 'T-D'AND
USERID <> '?'
GROUP BY USERID
HAVING COUNT(USERID) > 5

It wouldn't run, but if I remove CRTN_DTTM from the first line, then it works fine. Please advise, thank you.
1 REPLY
Junior Contributor

Re: sub queries

If i understand correctly then you need an OLAP function instead of an aggregate:

SELECT USERID, CRTN_DTTM, COUNT(USERID) OVER (PARTITION BY USERID) AS TICKET_GENERATED
FROM VEMEDY_SYM_TICKET
WHERE CRTN_DTTM>='2008-07-21 00:00:00' AND CRTN_DTTM<='2009-01-20 00:00:00' AND
DESK = 'TE' AND SYMP_SERVICE NOT IN ('DIAL','INTERNET PORTABLE', 'INTERNET ST') AND
ORIGIN = 'IC' AND
DESK_ALIAS = 'T-D'AND
USERID <> '?'
QUALIFY TICKET_GENERATED > 5

Dieter