Solved

ORACLE SQL:  Add a count to query

Posted on 2010-11-10
2
425 Views
Last Modified: 2012-05-10
Experts,

Can someone please help me out with my SQL statement?  My SQL output is showing 4 different ticket ID's but I want it to use the count feature.  Should show just the number 4.

Thanks!!

Using Oracle 10g
(SELECT DISTINCT (RELATED_TICKET_ID)

FROM ARADMIN.EMAIL_REPORTING 

WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY) 

    AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) 

    AND MESSAGE_TYPE = 1 

    AND OWNER_GROUP = 'National-Carrier Operations'

    AND RELATED_TICKET_ID LIKE 'MNT%'

OR UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY) 

    AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) 

    AND MESSAGE_TYPE = 0 

    AND OWNER_GROUP = 'National-Carrier Operations'

AND RELATED_TICKET_ID LIKE 'MNT%' 

    AND MAILBOX_NAME IS NOT NULL)

Open in new window

0
Comment
Question by:Maliki Hassani
2 Comments
 
LVL 11

Accepted Solution

by:
David Kroll earned 500 total points
ID: 34107004
(SELECT COUNT(DISTINCT (RELATED_TICKET_ID))
FROM ARADMIN.EMAIL_REPORTING
WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
    AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
    AND MESSAGE_TYPE = 1
    AND OWNER_GROUP = 'National-Carrier Operations'
    AND RELATED_TICKET_ID LIKE 'MNT%'
OR UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
    AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
    AND MESSAGE_TYPE = 0
    AND OWNER_GROUP = 'National-Carrier Operations'
AND RELATED_TICKET_ID LIKE 'MNT%'
    AND MAILBOX_NAME IS NOT NULL)
0
 

Author Closing Comment

by:Maliki Hassani
ID: 34107017
Sweeet,  Thanks
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now