SQL Query to Count Total Assets Limited to Visual Test & .NET

I used this query to count Total Assets per project from the database:

Select Count(TPNAME) as "Total Asset", TPASSOCIATEDPROJECTID as "Project ID"

From STW_ASSETMAIN

Group By TPASSOCIATEDPROJECTID

Now I need to limit the assets to Visual Test & .NET only, Could I know how to do that?

In the table design, there is TPOBJECTTYPE but its type is Integer and it doesn't have a relation with another database. 

  • Suggested Answer

    Hi

    The following query may be of use:

    SELECT TPNAME, COUNT(TPNAME)-1 As Versions, TPASSOCIATEDPROJECTID,
    CASE
        WHEN TPOBJECTTYPE=42 THEN 'Visual Test'
        WHEN TPOBJECTTYPE=43 THEN 'Result'
        WHEN TPOBJECTTYPE=44 THEN '.Net Script'
    END AS AssetType
    FROM STW_ASSETMAIN
    WHERE TPOBJECTTYPE IN (42, 43, 44)
    GROUP BY TPNAME, TPASSOCIATEDPROJECTID, TPOBJECTTYPE

    You should be able to modify this to meet your needs.

    -Robert