Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement...
Multiple SQL Select Statement... Multiple SQL Select Statement...
Multiple SQL Select Statement...
Go Back  Xtreme Visual Basic Talk > > > Multiple SQL Select Statement...


Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2013, 07:25 AM
lidds lidds is offline
Centurion
 
Join Date: May 2004
Posts: 100
Default Multiple SQL Select Statement...


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:

Code:
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
Attached Images
File Type: png SQLTable.png (26.4 KB, 7 views)
File Type: png SQLWant.png (22.8 KB, 5 views)
Reply With Quote
  #2  
Old 08-08-2013, 09:16 AM
Gruff's Avatar
GruffMultiple SQL Select Statement... Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

I do not have any way of testing this but I would think you would use Sum(<field name Count>) for each field ending the whole query with a Group By commarea Instead of Count(*).

This should give you the counts of each field for each commArea.
__________________
Burn the land and boil the sea
You can't take the sky from me


~T
Reply With Quote
  #3  
Old 08-08-2013, 03:47 PM
lidds lidds is offline
Centurion
 
Join Date: May 2004
Posts: 100
Default

Thank you for getting back to me and I have mad this change which is cleaner. The problem is though if you look at my original post I have two different WHERE clauses so that I can get a different count.

This is the problem, basically how to add the column of the second SQL statement with a different WHERE clause to the same return table linking by 'commArea' filed

Any ideas?

Thanks for your help

Simon
Reply With Quote
  #4  
Old 08-09-2013, 02:35 AM
DrPunk's Avatar
DrPunkMultiple SQL Select Statement... DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Commonly, multiple counts are done in queries using CASEs. The CASE allows you to have the different WHERE clauses. When using CASEs to COUNT it will often use a SUM instead. The CASE returning 1 (to Sum) when the conditions to COUNT are met and 0 when not.

Code:
SELECT commArea, COUNT(*) as totalAreaCount,
SUM(CASE WHEN commStatusID=4 OR commStatusID=5 OR commStatusID=8 THEN 1 ELSE 0 END) AS areaCount,
SUM(CASE WHEN (commStatusID=4 OR commStatusID=5 OR commStatusID=8) AND commAppEng IS NOT NULL THEN 1 ELSE 0 END) AS engAppCount
FROM commentsTbl 
WHERE (commReviewID=@review AND original='Yes' AND commCommited <> '-1' and inSession='False') 
GROUP BY commArea
I haven't done all the counts of your query there, but you should be able to work out how to do the other two. I think that should return what you are after. I might have made a mistake somewhere.
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement... Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement... Multiple SQL Select Statement...
Multiple SQL Select Statement...
Multiple SQL Select Statement...
 
Multiple SQL Select Statement...
Multiple SQL Select Statement...
 
-->