[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Syntax Query 2

Posted on 2011-10-21
5
Medium Priority
?
218 Views
Last Modified: 2012-05-12
Heyas,

I was wondering if their is anyway to list how many rows match the following criteria listed in bold below, below the actual output of the script.

select *
From universe
(
Select id, name, position, age
From Galaxies
Where Galaxy = 'Milkyway'
And Stars Not Like('ne%')
And Comets Like('c%')

UNION
Select id, name, position, age
From Galaxies
Where Galaxy = 'Andromeda'
And Stars Not Like('ne%')
And Comets Like('c%')

) as Data

So the format would like
Table Data

Then

Print Num of Filtered Records Stars Not Like = 21032
Print Num of Filtered Records Comets Like = 10654

I have tried using the select @@rowcount method but that doesn't work.

Any assistance would be much appreciated.

Thank you.


0
Comment
Question by:Zack
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 37005578
@@rowcount returns record count of the whole query. It won't dig down into sub-queries.

If you want to get the count of sub-query, you need to think about executing those query separately and get the count immediately after execution. But that may disturb the actual functionality of that query.

If you really want that information, we can achieve the same in this way.
1. Store the result of two queries (like & not like) in two table variables.
2. Read from those table in your main query
3. To get the count result write other queries

Script below
declare @table1 table
(
	id		int, 
	name	varchar(20), 
	position	varchar(20), 
	age		int
)
declare @table2 table
(
	id		int, 
	name	varchar(20), 
	position	varchar(20), 
	age		int
)

insert into @table1
	Select id, name, position, age
	From Galaxies
	Where Galaxy = 'Milkyway'
	And Stars Not Like('ne%')
	And Comets Like('c%')

insert into @table2
	Select id, name, position, age
	From Galaxies
	Where Galaxy = 'Andromeda'
	And Stars Not Like('ne%')
	And Comets Like('c%')

select *
From universe
(
select * from @table1
UNION
select * from @table2
) as Data

select 'Print Num of Filtered Records Stars Not Like = ' + ltrim(rtrim(cast(count(*) as varchar(10))) from @table1
select 'Print Num of Filtered Records Comets Like = ' + ltrim(rtrim(cast(count(*) as varchar(10))) from @table2

Open in new window


0
 

Author Comment

by:Zack
ID: 37005693
Could you do this if you have readonly permissions to the database

Thank you.
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 37005706
If you plan to create or modify an existing stored procedure, you should have permission to do that.

We declare only table variables (not physical tables), So table permission won't affect this

Raj
0
 

Author Closing Comment

by:Zack
ID: 37005713
Thank you for you help. I'll give this a go.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 37005716
Glad as I could guide you
Raj
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question