Sql Query - count

vbnetcoder
vbnetcoder used Ask the Experts™
on
I have the following SQL Query.  I want to return ONLY the records where the join only returns 1 record from the CompanySentResume table.

Select Company.CompanyID as CompanyID, Name,Email, SentResumeDate as "Resume Sent Date", FirstName From Company  
LEFT JOIN CompanySentResume
ON Company.CompanyID = CompanySentResume.CompanyID
 Where InvalidEmail = 0 AND DoNotContact ='0'
Order by SentResumeDate, CompanyID
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
In order to get the count i can do this but not sure how to put it all together:

SELECT[CompanyID]
      ,Count([SentResumeDate])
  FROM [JobSearch].[dbo].[CompanySentResume]
  Group By CompanyID
  order by Count(SentResumedate) desc
Shaun KlineLead Software Engineer

Commented:
You can add a HAVING clause to you GROUP BY statement:
SELECT[CompanyID]
       ,Count([SentResumeDate]) 
   FROM [JobSearch].[dbo].[CompanySentResume]
   Group By CompanyID 
   HAVING COUNT([SentResumeDate]) = 1
   order by Count(SentResumedate) desc 

Open in new window


You can then include this as a subquery to your original query.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>I want to return ONLY the records where the join only returns 1 record from the CompanySentResume table.
Note the use of table aliases and indenting, and give this a whirl..

Select c.CompanyID as CompanyID, Name,Email, SentResumeDate as [Resume Sent Date], FirstName, Count(csr.CompanyID) as csr_count
FROM Company c
   LEFT JOIN CompanySentResume csr ON c.CompanyID = csr.CompanyID
Where InvalidEmail = 0 AND DoNotContact ='0'
GROUP BY c.CompanyID, Name, Email, SentResumeDate, FirstName
HAVING Count(csr.CompanyID) = 1
Order by SentResumeDate, CompanyID
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
not much point in the left join, if you require this:
HAVING Count(csr.CompanyID) = 1

Author

Commented:
Jim Horn,

I believe that is on the right track but I don't think it take into consideration that the send resume date could be different and in most cases WILL be different.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>the send resume date could be different and in most cases WILL be different.
Not really sure what you mean here.  It would be helpful if you can post a data mockup of both tables, and your expected output.

Experts here are not connected to your data source, and since there's not a data mockup to work with we are limited to posting air code based on a wompload of assumptions.

Author

Commented:
Here you go.  There can be on value in the Company table to many in the CompanySentResume table
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
This is a complete guess.
Perhaps you are looking for a way to only list "the most recent SentResumeDate"? The following query uses 2 analytic functions:
row_number() - by sorting in descending date order a value of 1 identifies "the most recent  SentResumeDate"
count() over(). - this is used to count the number of resume's sent, it's optional I think.
SELECT
      c.CompanyID
    , c.Name
    , c.Email
    , csr.SentResumeDate            AS [MOST RECENT Resume Sent Date]
    , c.FirstName
    , COALESCE(csr.resume_count, 0) AS resume_count
FROM Company c
      LEFT JOIN (
                  SELECT
                        CompanyID
                      , SentResumeDate
                      , ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY SentResumeDate DESC) AS rn
                      , COUNT(*) OVER (PARTITION BY CompanyID)                                  AS resume_count
                  FROM CompanySentResume
            ) csr
                  ON c.CompanyID = csr.CompanyID
                        AND rn = 1
WHERE c.InvalidEmail = 0
      AND c.DoNotContact = '0'
ORDER BY
      csr.SentResumeDate
    , c.CompanyID

Open in new window

If this guess isn't useful, would you please supply sample data and expected result.

The fastest way to resolve SQL related question is to supply sample data (data per table, suitable for inserts)
and the provide the "expected result"

This is a proven combination to get results.

Author

Commented:
I was able to use this to get what i needed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial