Solved

Syntax for Count function as a subquery

Posted on 2011-03-23
3
369 Views
Last Modified: 2012-05-11
Hello all.  I have been researching this for quite some time, however, cannot seem to find anything that pertains to my specific question.  I need to write a subquery within the following to only pull records where the account number (customeraccountnumber) appears more than once based on the attached query criteria sample.  Any and all assistance/feeback is greatly appreciated!

Sincerely,
Nikki
SELECT i.[createdbyname],
              ,i.[createdon]
              ,i.[accountidname]
             ,i.[contacttypeidname]
             ,i.[customeraccountnumber]
            ,i.[customercategoryname]
            ,i.[customerstatusname]
            ,i.[primaryreasondetailname]
            ,i.[primaryreasonforcallname]
           ,i.[recordtypename]
           ,i.[secondaryreasondetailname]
          ,i.[secondaryreasonforcallname]            
FROM [Filteredcustomerinteraction] i
where name not like '%no call%' 
and i.name not like '%ASA%' 
and i.name not like '%Wrong department%'
and i.contacttypeidname = 'Call - Inbound'
and i.primaryreasondetailname not like '%transferred call%' 
and i.createdon >= '03/21/2011'
and i.createdon < '03/23/2011'
and i.siteid = '14'

Open in new window

0
Comment
Question by:Nikki28838
3 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 35202897
Maybe give this a try... (add to your query)

and 1 < (SELECT COUNT(*) FROM FilteredCustomerinteraction x WHERE x.customeraccountnumber = i.customeraccountnumber )
0
 
LVL 40

Accepted Solution

by:
Sharath earned 125 total points
ID: 35204058
try this.
SELECT i.[createdbyname], 
       i.[createdon], 
       i.[accountidname], 
       i.[contacttypeidname], 
       i.[customeraccountnumber], 
       i.[customercategoryname], 
       i.[customerstatusname], 
       i.[primaryreasondetailname], 
       i.[primaryreasonforcallname], 
       i.[recordtypename], 
       i.[secondaryreasondetailname], 
       i.[secondaryreasonforcallname] 
  FROM [Filteredcustomerinteraction] i 
 WHERE i.name NOT LIKE '%no call%' 
       AND i.name NOT LIKE '%ASA%' 
       AND i.name NOT LIKE '%Wrong department%' 
       AND i.contacttypeidname = 'Call - Inbound' 
       AND i.primaryreasondetailname NOT LIKE '%transferred call%' 
       AND i.createdon >= '03/21/2011' 
       AND i.createdon < '03/23/2011' 
       AND i.siteid = '14' 
       AND i.[customeraccountnumber] IN (  SELECT i1.[customeraccountnumber] 
                                             FROM [Filteredcustomerinteraction] i1 
                                            WHERE i1.name NOT LIKE '%no call%' 
                                                  AND i1.name NOT LIKE '%ASA%' 
                                                  AND i1.name NOT LIKE '%Wrong department%' 
                                                  AND i1.contacttypeidname = 'Call - Inbound'
                                                  AND i1.primaryreasondetailname NOT LIKE '%transferred call%'
                                                  AND i1.createdon >= '03/21/2011' 
                                                  AND i1.createdon < '03/23/2011' 
                                                  AND i1.siteid = '14' 
                                         GROUP BY i1.[customeraccountnumber] 
                                           HAVING COUNT(* ) > 1)

Open in new window

0
 

Author Closing Comment

by:Nikki28838
ID: 35206760
Thank you so much!  Looking at it, it all makes sense!  I will be able to use this logic over and over!  You are a lifesaver!  

Sincerely,
Nikki
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

816 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

8 Experts available now in Live!

Get 1:1 Help Now