How to get % from two field - SQL Server Forums

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 get % from two field

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2009-09-18 : 11:57:30

Hi All

I have a query it return me correct value but i want to add new column which is a percentage based on "REQ with no Test" and "Total Requirements". But it is not working. Please chec bold code which is modification.



SELECT
"Parent Folder",
Count("Parent Folder") As "Total Requirements",
Sum("Test Coverage") As "Total Test Cases",
Count(case when "Test Coverage" =0 Then "Test Coverage" End) As "REQ with no Test Cases" , Sum("Test Case Validated") AS "Test Case Validated" , ("REQ with no Test/
"Total Requirements") AS "Percentage" 
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",
------
       (SELECT COUNT(CASE WHEN TEST.TS_USER_24  <> '' Then TEST.TS_USER_24 END)
        FROM TEST, REQ_COVER
        WHERE REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID AND REQ.RQ_REQ_ID  = REQ_COVER.RC_REQ_ID
        )AS "Test Case Validated"
-------
  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"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-18 : 12:53:50

You can't refer to a column alias in the same statement that defines the column alias. So try this:

[REQ with no Test] / isNull(nullif(Sum([Test Case Validated]),0),0) AS [Percentage]

EDIT:
the nullIf is to protect against a "devide by zero" error.
the isNull is assuming you want a zero returned of sum is zero.

Be One with the Optimizer
TG

shahid09
Starting Member

35 Posts

Posted - 2009-09-18 : 13:37:14

Thanks TG,

I am getting invalid column name 'REQ with no Test Cases' error



SELECT
"Parent Folder",
Count("Parent Folder") As "Total Requirements",
Sum("Test Coverage") As "Total Test Cases",
Count(case when "Test Coverage" =0 Then "Test Coverage" End) As "REQ with no Test Cases" , Sum("Test Case Validated") AS "Test Case Validated",
[REQ with no Test Cases] / isNull(nullif(Sum([Total Requirements]),0),0) AS [Percentage]
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",
------
       (SELECT COUNT(CASE WHEN TEST.TS_USER_24  <> '' Then TEST.TS_USER_24 END)
        FROM TEST, REQ_COVER
        WHERE REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID AND REQ.RQ_REQ_ID  = REQ_COVER.RC_REQ_ID
        )AS "Test Case Validated"
-------
  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"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-18 : 13:45:56

Oh, I didn't notice that that was an alias as well. You just need to do the same thing I did with [Total Requirements]. Replace the alias with the expression that defines the alias.

Be One with the Optimizer
TG

shahid09
Starting Member

35 Posts

Posted - 2009-09-18 : 16:30:22

TG,

I am sorry. I couldn't understand your point. You mean i should use "REQ with no Test Cases" as [REQ with no Test Cases] where i define it as an alias ?

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources