How do I count rows of a subquery in MSSQL?

How do I count number of returned rows in MSSQL within a subquery?

Error:  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
SELECT navID, navURL FROM navSystemA where id IN
	(SELECT navID,(SELECT COUNT(*)) AS navCount  FROM navSystemAMap where storeID =
	(SELECT storeID FROM store_profile WHERE subdomain_name=@subdomain_name)
	AND active=1)

Open in new window

trumpmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChetOS82Commented:
You have to break it into two queries.  On that gets navID, and another which gets the COUNT(*)
0
trumpmanAuthor Commented:
@Chet0S82

I get this error:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'navCount'.

if I add a second query like:

SELECT COUNT(*) AS navCount  FROM navSystemAMap where storeID =
        (SELECT storeID FROM store_profile WHERE subdomain_name=@subdomain_name)
        AND active=1
0
fanopoeCommented:
this should work:

SELECT navID, navURL, tbl.Total
FROM navSystemA join (SELECT navID, count(*) Total
                                FROM navSystemAMap
                                where storeID = (SELECT storeID
                                                         FROM store_profile
                                                         WHERE subdomain_name=@subdomain_name)
                                AND active=1
                                GROUP BY navID) tbl
                        on id = tbl.navID


hth
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

trumpmanAuthor Commented:
@fanopoe:

Should <%#Eval("Total") %> return this value in my .aspx page?  It only returns 1 when there is more than 1 row of results.

0
SharathData EngineerCommented:
The query provided by fanopoe is correct.

Do you have more than one record in navSystemAMap  table for same navID?
0
trumpmanAuthor Commented:
No.  I need to know the number of rows for same storeID not navID.
0
fanopoeCommented:
if you're looking for store count, try this:
SELECT navID, navURL, tbl.TotalStore
FROM navSystemA join (SELECT navID, sub1.TotalStore
                      FROM navSystemAMap
                      where storeID = (SELECT storeID, count(*) TotalStore
                                       FROM store_profile
                                       WHERE subdomain_name=@subdomain_name
                                       group by storeID) sub1
                      AND active=1) tbl
                on id = tbl.navID
0
SharathData EngineerCommented:
fanope - your query will give errors as the column used in group by clause is not present in SELECT clause of your inner query.
ALso the below line in your code is not correct .
where storeID = (SELECT storeID, count(*)

trumpman - You want number of rows for same store id.
but in the first SELECT clause, you have navID, navURL. for same navID, if you have more than one storID, how do you want your result set. give your desired sample set so that i can write query.
0
fanopoeCommented:
you're right, this is better and easier to follow anyway:
SELECT n.navID, n.navURL, tblB.TotalStore
from navSystemA n
join (SELECT navID, storeID
      FROM navSystemAMap) tblA on n.id = tblA.navID
join (SELECT storeID, count(*) TotalStore
      FROM store_profile
      WHERE subdomain_name=@subdomain_name
      group by storeID) tblB on tblA.storeID = tblB.storeID
0
SharathData EngineerCommented:
for same navID if you have more than one storID, you will have more than one row with fanopoe's query like below.
navID navURL TotalStore
1         abc       10
1         abc       20
1         abc       30
You should include storeID also in your SELECT clause to know which count is for which store

SELECT n.navID, n.navURL,tblB.storeID,tblB.TotalStore
from navSystemA n
join (SELECT navID, storeID
      FROM navSystemAMap) tblA on n.id = tblA.navID
join (SELECT storeID, count(*) TotalStore
      FROM store_profile
      WHERE subdomain_name=@subdomain_name
      group by storeID) tblB on tblA.storeID = tblB.storeID

This may help you.
0
Mark WillsTopic AdvisorCommented:
The subquery must return a single value. Doing it as an inline query, you original code goes close, just need to move that NavID in the second query to be a condition, not a result... or.... the navcount should be moved (maybe to a having clause). Assuming it is the count you actually want, then... similarly, need to make sure there is only one store id (or change the second subquery to be an "in").

So, to get the correct answer, you probably do need to explain some of those data relationships a bit more as to what columns you are really seeking and on what conditions.


SELECT navID, navURL,
      (SELECT COUNT(*))  FROM navSystemAMap where navSystemAMap.Navid = navSystemA.navid and storeID =
      (SELECT top 1 storeID FROM store_profile WHERE subdomain_name=@subdomain_name)
      AND active=1)  as navcount
 FROM navSystemA


- could also do it by joins, but prefer to find out the real columns and the real connections between the datasources, in the meantime the inline query will work OK.
0
trumpmanAuthor Commented:
Hey guys,  Thank you for your assistance.  I will clarify my column relationships per @mark wills.

navSystemAMap contains storeID,navID, active.  StoreID connects to store_profile to get store information.  navID connects to id in navSystemA for it's attributes.  However, these connections should not be made and/or counted in the event that active = 0 for any combination of storeID and navID in navSystemAMap

Sorry for the vagueness.
0
Mark WillsTopic AdvisorCommented:
OK, that's does clear up a few things... One more for you, given your explanation, what is the "driving" table - do you want all rows from navsystema and gather the relevant other information, or do you want navSystemAMap to drive the selection process ?

The in-line query I think still works,

and for the join version,

select navsystema.navid, navsystema.navurl, count(*)
from navsystema  
inner join navsystemamap on navsystema.navid = navsystemamap.navid and navsystemamap.active = 1
inner join store_profile on navsystemamap.storeid = store_profile.storeid
group by navsystema.navid, navsystema.navurl

of course, would be better with alias's and arguably a "where" clause for active... e.g.

select n.navid, n.navurl, count(*)
from navsystema n  
inner join navsystemamap m on n.navid = m.navid
inner join store_profile s on m.storeid = s.storeid
where m.active = 1
group by n.navid, n.navurl

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
Trumpman -
Provide some sample data (2-3 records) in all the tables. and your desired output.
0
trumpmanAuthor Commented:
<%#Eval("navId") %>  produces the following error in my C# page:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'navId'.

count is returned without producing an error.  Am I making a mistake in my syntax?  If necessary, I can start a new thread for this new question.

Thank you.

0
SharathData EngineerCommented:
Did you run my query. What is the output of that?
0
trumpmanAuthor Commented:
The output in SQL is displaying correctly.  This,  however, is not the case when the results are called from my code-behind.

0
SharathData EngineerCommented:
>> The output in SQL is displaying correctly.
That means you got the solution for whatever the question you have asked.
>> This,  however, is not the case when the results are called from my code-behind.

That means, i think you are trying to retrieve the result set from any front end application. If you have problem in this retrieval, this may be an issue with your UI not the database query. so close this quesiton and open a new question qith your issue in the correct zone.
0
Mark WillsTopic AdvisorCommented:
If your SQL is now working, then yes, it might be time to move to the front end - different skills, likely to be different experts as well...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.