Solved

Syntax for Count function as a subquery

Posted on 2011-03-23
3
373 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

691 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