To find count from sql table.. Please Help..

Question

1.00/5 (1 vote)

See more:

SQL

id  candid  jobid    status      date
1    11      11j    Introduced   20/04/2014
2    11      11j    Interviewed  22/04/2014 
3    12      11j    Introduced   22/04/2014
4    13      11j    Introduced   22/04/2014
5    12      11j    Interviewd   24/04/2014
6    12      11j    Rejected     24/04/2014

i want to count introduced candidates only which r not interviewd..
i.e. In above table i have 3 introduced candidates, but 2 of them are interviewd that means i need only one introduces candidate having candid = 13

I need the sql query to count only introduce candidates which are not interviewd, rejected..

Please anybody help me with this...
I am wating for reply..
Please..

I am Having table like this:

HTML

id  candid  candname        status      date       time     location    jobcode
1   12      hhhhhhhhhh      Introduce   2014-05-21 14:0     NewYork     10JN 
3   12      hhh             Reject      2014-05-21 15:0 AM  London      10JN
4   12      hhhhhhhhhh      Interview   2014-05-21 15:0 PM  Chicago     10JN
5   11      Pinky Bare      Introduce   2014-05-21 65:6     India       10JN 
6   11      Pinky Bare      Interview   2014-05-21  4:56 AM             10JN
7   13      chetan Tae      Introduce   2014-05-21  4:54 AM Nagpur      faOl
8   13      chetan Tae      Interview   2014-05-21  3:45    Pune        faOl
9   14      manisha mane    Introduce   2014-05-21  3:33 PM Pune        faOl
10  18      ranju gondane   Introduce   2014-05-28  3:44    Nagpur      AQW-06
12  18      ranju gondane   Interview   2014-05-28  5:45    45454       AQW-06
13  18      ranju gondane   Reject      2014-05-28 43:43    rsds        AQW-06
14  19      vandanna rai    Introduce   2014-05-28  7:7     yyyr        AQW-06

if i use query

SQL

SELECT COUNT(*) FROM [tablename] WHERE (jobcode='AQW-06') AND ([status] <> 'Interview' AND [status] <> 'Reject' AND [status] <> 'ON-Hold' AND [status] <> 'Hire')

I get count 2 for introduce candidates..

if the candidate is interviewd after introduce, it will not counted as Introduce..

Posted 28-May-14 21:20pm

AshwinGanvr

Updated 28-May-14 22:16pm

v3

Add a Solution

Comments

King Fisher 29-May-14 3:30am

Show your ResultSet

syed shanu 29-May-14 3:35am

What is the status for Introduced candidate who is not attend interview .Is that Candid 13 means who is not interviewd ?
if so your query will be likse this
select count(*) from YOUR_TABLE_NAME where Status='Introduced' AND candid ='13'

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-05-28T21:36:00

Solution 1

Try

SQL

SELECT COUNT(*) FROM MyTable WHERE status <> 'Interviewed' AND status <> 'Rejected'

Posted 28-May-14 21:36pm

OriginalGriff

**Nirav Prabtani** · Answer 2 · 2014-05-28T21:38:00

Solution 2

SQL

select COUNT(*) FROM tableName where Status = 'What ever status you want'

--or

select COUNT(*) FROM tableName where Status <> 'What ever status you do not want want'

Posted 28-May-14 21:38pm

Nirav Prabtani

soumyajayaraj · Answer 3 · 2014-05-28T21:51:00

Solution 3

You could try this if you want to remove records with interviewed status

SQL

SELECT count(*) FROM table_name AS t1 WHERE t1.status ='introduced' AND NOT EXISTS
(SELECT candid FROM table_name WHERE status= 'interviewed' AND candid = t1.candid)

Posted 28-May-14 21:51pm

soumyajayaraj

Updated 28-May-14 21:54pm

v2

_Asif_ · Answer 4 · 2014-05-28T22:37:00

Solution 4

Try this SQL

SQL

SELECT *
from  TABLE T 
      Inner Join 
      (
        SELECT Candid
        from TableName
        group by Candid
        having count(*) = 1
      ) tmp ON T.Candid = tmp.Candid

Posted 28-May-14 22:37pm

_Asif_

AshwinGanvr · Answer 5 · 2014-05-29T00:45:00

SQL

select count(x.candid) numofcandidates, x.statusnum
from
(select candid, max(case when status = 'Reject' then 3
                         when status = 'Interview' then 2
                         when status = 'Introduce' then 1 end) statusnum
from [tablename] t
where jobcode = 'AQW-06'
group by candid) x
group by x.statusnum;