Solved

Syntax for Count function as a subquery

Posted on 2011-03-23
3
367 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:xDJR1875
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

18 Experts available now in Live!

Get 1:1 Help Now