Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql Query - count

Posted on 2014-08-07
10
Medium Priority
?
581 Views
Last Modified: 2014-08-08
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
0
Comment
Question by:vbnetcoder
  • 5
  • 2
  • 2
  • +1
10 Comments
 

Author Comment

by:vbnetcoder
ID: 40246150
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
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40246163
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.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40246167
>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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40246179
not much point in the left join, if you require this:
HAVING Count(csr.CompanyID) = 1
0
 

Author Comment

by:vbnetcoder
ID: 40246204
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.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40246272
>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.
0
 

Author Comment

by:vbnetcoder
ID: 40246412
0
 

Author Comment

by:vbnetcoder
ID: 40246414
Here you go.  There can be on value in the Company table to many in the CompanySentResume table
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40247616
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.
0
 

Author Closing Comment

by:vbnetcoder
ID: 40248899
I was able to use this to get what i needed
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 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