how to select the data from sql table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

My table is

HTML

projecttaskid    status      projectid resid
-----------------------------------------------
1                on hold        9       43
2                in progress    9       44
3                in progress    9       43
4                draft          9       43
5                on hold        10      43
----------------------------------------------

Now I want

taskcount (having status!=draft) of resid 43 as [assignedTaskcount] and total taskcount (having status!=draft) as[Totaltaskcount]
where projectid=9 in a single query

I want the output as

HTML

assignedTaskcount     Totaltaskcount
----------------------------------------
    2                    3
----------------------------------------

Please help by giving the query.I can't use resid as direct in query,I've to fetch it by joining with other table.
But for your easy understanding,just help me on this table.

Posted 5-Jun-15 1:39am

souvikcode

Updated 5-Jun-15 1:41am

v2

Add a Solution

2 solutions

Solution 1

a quick solution could be

SQL

SELECT (SELECT  COUNT(projecttaskid) FROM yourTable WHERE status != 'draft' AND projectid=9 and  resid = 43) assignedTaskcount,
        (SELECT  COUNT(projecttaskid) FROM yourTable WHERE status != 'draft' AND projectid=9)  Totaltaskcount

for best performance you can create temp table and insert all your data in that table and then make these queries on that table

SQL

DECLARE @temp TABLE
(
	projecttaskid   BIGINT,
	status      VARCHAR(100),
	projectid BIGINT,
	resid BIGINT
)

INSERT INTO @temp ( projecttaskid ,status ,projectid ,resid)
SELECT * FROM yourTable WHERE projectid = 9 and status != 'draft'

SELECT (SELECT  COUNT(projecttaskid) FROM @temp WHERE  resid = 43) assignedTaskcount,
        (SELECT  COUNT(projecttaskid) FROM @temp)  Totaltaskcount

Posted 5-Jun-15 2:34am

kashif Atiq

Updated 5-Jun-15 2:46am

v5

Comments

souvikcode 6-Jun-15 14:01pm

Thanks for support

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)

Richard Deeming · Accepted Answer · 2015-06-05T03:45:00

Solution 2

The simplest solution is to combine a SUM with a CASE statement:

SQL

SELECT
    SUM(CASE WHEN resid = 43 THEN 1 ELSE 0 END) As assignedTaskcount,
    COUNT(1) As Totaltaskcount
FROM
    YourTable
WHERE
    projectid = 9
And
    status != 'draft'
;

Posted 5-Jun-15 3:45am

Richard Deeming

Comments

souvikcode 5-Jun-15 13:22pm

many thanks.
I was stupid to think use status != 'draft' in count(case).
I should have think I can count on resid and use status in where clause as status!=draft required for both assigned and total.
Thanks and can you please name sites in which I can take challenge of sql query with solution?