-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: How to retrieve results of aggregate functions from query?
PostPosted: Mon Jul 13, 2009 6:46 am 
Newbie

Joined: Mon Jul 13, 2009 6:39 am
Posts: 1
Hello All,

I usually develop the middle-tier and front-end of our ASP.NET - C# - nHibernate - SQL Server project but I am venturing more and more into the back-end. I am not new to development but I am relatively new to all things database and so I would like some advice regarding a relatively complex query.

I have the following query, and it works in SQL Server 2005 Management Studio.The parts of interest are the first INNER JOIN clause and the Select statement. Our database has a TestSetResults table with a primary key of TestSetResultID, a TestCases table with a primary key of TestCaseID and a TestCaseResults table with keys of TestSetResultID and TestCaseID. In this way a Test Set Result record is associated with many Test Case Result records. The purpose of the query is to get back a maximum of 50 TestSetResult records along with the number of tests completed (a count of the associated TestCaseResult records) and the number of test failures (a count of all the associated TestCaseResult records with a status of "failure") for each test run result. (A colleague provided me with this query, and I'm not entirely sure how it works).

Code:
SELECT TOP (50)  {TSR.*}, {t1.*}, {t2.*}, {t3.*}, {t4.*}, {t5.*}, TestCounts.TestsCompleted, TestCounts.NumFailures

FROM TestSetResults AS TSR

INNER JOIN (
        SELECT
            TCR.TestSetResultID,
            COUNT (TCR.TestCaseID) AS TestsCompleted,
            SUM(CASE WHEN TCR.TestCaseStatus == 'failure' THEN 1 ELSE 0 END) AS NumFailures
        FROM TestCaseResults TCR
        GROUP BY TCR.TestSetResultID)
        AS TestCounts ON TestCounts.TestSetResultID =TSR. TestSetResultID

INNER JOIN table1 AS t1 ON TSR.t1ID = t1.ID
INNER JOIN table2 AS t2 ON TSR.t2ID = t2.ID
INNER JOIN table3 AS t3 ON t2.t3ID = t3.ID
INNER JOIN table4 AS t4 ON TSR.t4ID = t4.ID
INNER JOIN table5 AS t5 ON t4.t5ID = t5.ID

WHERE  pID IN ('...')

ORDER BY TSR.StartDateTime DESC


The trouble I am having is that I can't get the values of TestCounts.TestsCompleted and TestCounts.NumFailures from the query result. I suspect that there is a far better way of doing this by using the SUM and COUNT aggregate functions in the Select clause.

The code that I am using to perform this query looks something like this...

Code:
     if (this.session.IsOpen)
     {
       using (ITransaction tx = this.session.BeginTransaction())
       {
         ISQLQuery nHibernateQuery = this.session.CreateSQLQuery(this.Query.Sql.Statement);
         AddEntitiesToQuery(ref nHibernateQuery);

         this.query.Result = nHibernateQuery.List();
         tx.Commit();
       }
     }

I would be very grateful is somebody could explain how I should get
the TestCounts values from the query result.

Thanks in advance,

Mikey


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.