Multiple SQL Select Statement...

[lidds]

I am having a problem with an stored procedure that has multiple SQL statements. I am not sure if it actually requires multiple statements, but hopefully in explaining my problem others may be able to offer either a solution or a better way to do this.

Below is my stored procdure:

CREATE PROCEDURE [dbo].[spSimonTest] @review as nvarchar(100)
AS
SELECT commArea, COUNT(*) as areaCount, count(commAppEng) as engAppCount, count(commAppCompany) as companyAppCount, count(commAppClient) as clientAppCount FROM commentsTbl WHERE (commReviewID=@review AND original='Yes' AND commCommited <> '-1' and inSession='False') and (commStatusID=4 OR commStatusID=5 OR commStatusID=8) GROUP BY commArea
SELECT commArea, COUNT(*) as totalAreaCount FROM commentsTbl WHERE (commReviewID=@review AND original='Yes' AND commCommited <> '-1' and inSession='False') GROUP BY commArea

What this returns is shown in attached file 'SQLTable.png'

What I want is shown in attached file 'SQLWant.png'

Hopefully the screen images will explain what I am looking to achieve.

Thanks in advance

Simon

 

[IanRyder]

Hi,

Since you are using different WHERE clauses on each of your Group Queries, what you need to do is to link the two queries together using a Join and then Select the required fields in your tabular format.

Here is an example of creating two separate queries which use Group By to summarise some data (from the same table) and then a Join is used to link the two together to create the final result. All you need to do here is use this concept to add the correct Where clauses and required fields from your own table to produce your final result:-

SELECT     GRP_Query_1.CustomerID, GRP_Query_1.TotalOrders, GRP_Query_2.TotalOtherValues
 
FROM         (SELECT     CustomerID, COUNT(*) AS TotalOrders
                       FROM          dbo.Orders
                       GROUP BY CustomerID) AS GRP_Query_1
 
INNER JOIN
 
                          (SELECT     CustomerID, COUNT(*) AS TotalOtherValues
                            FROM          dbo.Orders AS Orders_1
                            GROUP BY CustomerID) AS GRP_Query_2
 
ON GRP_Query_1.CustomerID = GRP_Query_2.CustomerID

Hope that helps.

Cheers,

Ian