Solved

Syntax for Count function as a subquery

Posted on 2011-03-23
3
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

739 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