How to query from the output of the another query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to query from the output of the another query

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2009-09-16 : 10:12:09

Hi All,

I have written following query:



SELECT  (SELECT B.RQ_REQ_NAME From REQ B WHERE REQ.RQ_FATHER_ID = B.RQ_REQ_ID) AS
"ParentFolder", REQ.RQ_REQ_ID AS "Requirement ID", REQ.RQ_REQ_NAME AS "Requirement Name",COUNT (REQ_COVER.RC_ITEM_ID) AS " Test Coverage"
FROM REQ
LEFT JOIN REQ_COVER
ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
WHERE REQ.RQ_TYPE_ID IN (3,4) AND REQ.RQ_REQ_PATH LIKE 'AAAAAD%'
GROUP BY REQ.RQ_FATHER_ID, REQ.RQ_REQ_ID,REQ.RQ_REQ_NAME, REQ.RQ_REQ_AUTHOR, REQ.RQ_REQ_DATE, REQ.RQ_REQ_TIME
ORDER BY "Parent Folder"

I get following output:

Parent Folder Requirement ID Requirement Name Test Case
BANK 12 Credit 46
BANK 13 Deposit 0
BANK 17 Cash 12
SCHOOL 19 Book 17
SCHOOL 20 Copy 0

Is there anyway I can modify my query to get unique record of all "Parent Folder " with some additional columns like
TotalPF : How many parent folders with same name
TotalTestCase: How many total test case for parent folders
TotalTestCasewith0value: How many Parent folder has 0 value in Test coverage

Like

Parent Folder TotalPF TotalTestCase TotalTestCasewith0value
BANK 3 58 1
SCHOOL 2 17 1

Can any one please assist me how to do it.

Regards,
SIK

Sachin.Nand

2937 Posts

Posted - 2009-09-16 : 10:19:14

Select parent,Count(Folder) as Folder,Sum(Test Case) as Test Case,Count(case when Test Case
=0 then Test Case end)as TotalTestCasewith0value from
(

ur original query

)T group by parent

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 12:12:58

quote:
Originally posted by Idera

Select parent,Count(Folder) as Folder,Sum(Test Case) as Test Case,Count(case when Test Case
=0 then Test ~~Case~~ end)as TotalTestCasewith0value from
(

ur original query

)T group by parent

PBUH

shahid09
Starting Member

35 Posts

Posted - 2009-09-16 : 13:07:58

Hi,
Thanks for the reply

I tried to run following query but getting an error "The Order By clause is invalid in views, inline functions, derived tables and subqueries unless TOP is also specified.


Select Count('Parent Folder') as "Folder",Sum('Test Case') as "Test Case",Count(case when 'Test Case'
=0 then 'Test Case' end)as "TotalTestCasewith0value" from
(

SELECT  (SELECT B.RQ_REQ_NAME From REQ B WHERE REQ.RQ_FATHER_ID = B.RQ_REQ_ID) AS
 "Parent Folder", REQ.RQ_REQ_ID AS "Requirement ID", REQ.RQ_REQ_NAME AS "Requirement Name",COUNT (REQ_COVER.RC_ITEM_ID) AS " Test Coverage" , REQ.RQ_REQ_AUTHOR As "Requirement Author", REQ.RQ_REQ_DATE + ' ' + REQ.RQ_REQ_TIME AS 'Creation Date'
FROM REQ
LEFT JOIN REQ_COVER
ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
WHERE REQ.RQ_TYPE_ID IN (3,4) AND REQ.RQ_REQ_PATH LIKE 'AAAAAD%'
GROUP BY REQ.RQ_FATHER_ID, REQ.RQ_REQ_ID,REQ.RQ_REQ_NAME, REQ.RQ_REQ_AUTHOR, REQ.RQ_REQ_DATE, REQ.RQ_REQ_TIME
ORDER BY "Parent Folder"


)T group by 'parent Folder'

Thanks,
Again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 13:19:48

quote:

Originally posted by shahid09

Hi,
Thanks for the reply

I tried to run following query but getting an error "The Order By clause is invalid in views, inline functions, derived tables and subqueries unless TOP is also specified.


Select Count('Parent Folder') as "Folder",Sum('Test Case') as "Test Case",Count(case when 'Test Case'
=0 then 'Test Case' end)as "TotalTestCasewith0value" from
(

SELECT  (SELECT B.RQ_REQ_NAME From REQ B WHERE REQ.RQ_FATHER_ID = B.RQ_REQ_ID) AS
 "Parent Folder", REQ.RQ_REQ_ID AS "Requirement ID", REQ.RQ_REQ_NAME AS "Requirement Name",COUNT (REQ_COVER.RC_ITEM_ID) AS " Test Coverage" , REQ.RQ_REQ_AUTHOR As "Requirement Author", REQ.RQ_REQ_DATE + ' ' + REQ.RQ_REQ_TIME AS 'Creation Date'
FROM REQ
LEFT JOIN REQ_COVER
ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
WHERE REQ.RQ_TYPE_ID IN (3,4) AND REQ.RQ_REQ_PATH LIKE 'AAAAAD%'
GROUP BY REQ.RQ_FATHER_ID, REQ.RQ_REQ_ID,REQ.RQ_REQ_NAME, REQ.RQ_REQ_AUTHOR, REQ.RQ_REQ_DATE, REQ.RQ_REQ_TIME



)T group by 'parent Folder'
ORDER BY Parent Folder

Thanks,
Again

also dont put "",'',.. around column names

shahid09
Starting Member

35 Posts

Posted - 2009-09-16 : 23:55:18

Hi All,

Thanks a lot. All information was very helpful. I have one more question
Following query returns me Test Coverage which is total number of test case for each requirement. Now i want to something to print how many test case field a with passed value.

Current SQL without new field addition


SELECT  (SELECT B.RQ_REQ_NAME From REQ B WHERE REQ.RQ_FATHER_ID = B.RQ_REQ_ID) AS
 "Parent Folder", REQ.RQ_REQ_ID AS "Requirement ID", REQ.RQ_REQ_NAME AS "Requirement Name",COUNT (REQ_COVER.RC_ITEM_ID) AS " Test Coverage" , REQ.RQ_REQ_AUTHOR As "Requirement Author", REQ.RQ_REQ_DATE + ' ' + REQ.RQ_REQ_TIME AS 'Creation Date'
FROM REQ
LEFT JOIN REQ_COVER
ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
WHERE REQ.RQ_TYPE_ID IN (3,4) AND REQ.RQ_REQ_PATH LIKE 'AAAAAD%'
GROUP BY REQ.RQ_FATHER_ID, REQ.RQ_REQ_ID,REQ.RQ_REQ_NAME, REQ.RQ_REQ_AUTHOR, REQ.RQ_REQ_DATE, REQ.RQ_REQ_TIME
ORDER BY "Parent Folder"

This information comes from another table.

I have comeup with following query but it is not working :)

[Code]

SELECT (SELECT B.RQ_REQ_NAME From REQ B WHERE REQ.RQ_FATHER_ID = B.RQ_REQ_ID) AS
"Parent Folder", REQ.RQ_REQ_ID AS "Requirement ID", REQ.RQ_REQ_NAME AS "Requirement Name",COUNT (REQ_COVER.RC_ITEM_ID) AS " Test Coverage" , REQ.RQ_REQ_AUTHOR As "Requirement Author", REQ.RQ_REQ_DATE + ' ' + REQ.RQ_REQ_TIME AS 'Creation Date' ,
(
Select COUNT(CASE WHEN TEST.TS_USER_24 = 'Passed' Then TEST.TS_USER_24 END)FROM TEST, REQ_COVER WHERE
TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
GROUP BY REQ_COVER.RC_REQ_ID ) as "Total Test case has Passed Value"
FROM REQ
LEFT JOIN REQ_COVER
ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
WHERE REQ.RQ_TYPE_ID IN (3,4) AND REQ.RQ_REQ_PATH LIKE 'AAAAAD%'
GROUP BY REQ.RQ_FATHER_ID, REQ.RQ_REQ_ID,REQ.RQ_REQ_NAME, REQ.RQ_REQ_AUTHOR, REQ.RQ_REQ_DATE, REQ.RQ_REQ_TIME
ORDER BY "Parent Folder"
[/code]

I am getting following error:

Subquery returned more than 1 value . This is not permitted when the sab query follows = .....

Thanks in advance,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources