How to get a sum of a count () funtction with using group by cluase in a sqlquery

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have a Table as below

ID Condition Document Employee EID Person PID Owner Status
----------------------------------------------------------------------------
20 Condition1 Payoff 1 Processor 7 ccc Suzanne 1 LPTLC Close
29 Condition2 Bank St Processor 7 Herity 30 LPTLC Close
40 Condition4 Payoff 1 Processor 7 jack 37 LPTLC Close
65 Condition5 Paystub Processor 7 David 47 LPTLC Open
81 Condition1 IAPLD Processor 7 Herity 30 LPTLC open

I need a query output as count of person where EID=7 and Status =Close .
Thats mean these persons have only status as Close, not Open

Result as 2 from ccc Suzanne,jack.
Here Herity Has status open and close so it discard.
select row which person has status field only Close,this specified person has no status open.
How can we get?

Posted 8-Mar-11 18:26pm

vipinsethumadhavan

Updated 8-Mar-11 19:46pm

v2

Add a Solution

2 solutions

Solution 1

Try:

SQL

SELECT 
  COUNT(ID)
FROM
  myTable
WHERE
  EID = 7
  AND
  Status = 'Close'

What;s the problem with this?

Posted 8-Mar-11 18:29pm

Sandeep Mewara

Comments

vipinsethumadhavan 9-Mar-11 0:38am

I already used above query. It is not possible because of then we get count as 3, sum of all close status rows as ID 20,29,40.
But we need another condition as here ID 40,81 person as same ,Herity so it is not need. Herity has status open and close.

Sandeep Mewara 9-Mar-11 1:01am

Not clear! :doh:

vipinsethumadhavan 9-Mar-11 2:06am

Question is simple.
For example
We have more than 100 clients . Each client have more than 1 documents with status close or Open. That is main thing.
Now I required output is print number of clients , these clients all documents status is close.
eg? if a client have 1(or more) document with status open and 1(or more) document with status close. Then this client exact result is pending. So it is not take.Thats all

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)

pankajupadhyay29 · Accepted Answer · 2011-03-08T18:42:00

Solution 2

Try this-

SQL

Select Count(PID) from (
SELECT 
  PID
FROM
  myTable
WHERE
  EID = 7
  group by PID
 having Min(Status)=Max(Status) and  Min(Status) = 'Close'
) as SelectedData;

[Edit]Edited for getting total count[/Edit]

--Pankaj

Posted 8-Mar-11 18:42pm

pankajupadhyay29

Updated 8-Mar-11 19:42pm

v3

Comments

vipinsethumadhavan 9-Mar-11 0:55am

Then I got 0 row

pankajupadhyay29 9-Mar-11 1:10am

how could you get 0 rows query using Count which will return at least 1 row either with value 0 or >0.

pankajupadhyay29 9-Mar-11 1:13am

ok now i got your point change the group by EID to Group by PID. I already made changes in solution you can use this now.

vipinsethumadhavan 9-Mar-11 1:29am

When change it, return more than one rows . Top of row or sum of rows not correct.

pankajupadhyay29 9-Mar-11 1:39am

could you please provide the output here.

pankajupadhyay29 9-Mar-11 1:42am

now i improve solution for your case check that.

vipinsethumadhavan 9-Mar-11 2:19am

Yes. Thank You . I got output

pankajupadhyay29 9-Mar-11 6:45am

your welcome.