?
Solved

How do I  count rows of a subquery in MSSQL?

Posted on 2008-11-06
19
Medium Priority
?
2,028 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:trumpman
  • 6
  • 6
  • 3
  • +2
19 Comments
 
LVL 18

Expert Comment

by:ChetOS82
ID: 22897723
You have to break it into two queries.  On that gets navID, and another which gets the COUNT(*)
0
 

Author Comment

by:trumpman
ID: 22897970
@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
 
LVL 11

Expert Comment

by:fanopoe
ID: 22898027
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:trumpman
ID: 22898571
@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
 
LVL 41

Expert Comment

by:Sharath
ID: 22902356
The query provided by fanopoe is correct.

Do you have more than one record in navSystemAMap  table for same navID?
0
 

Author Comment

by:trumpman
ID: 22903745
No.  I need to know the number of rows for same storeID not navID.
0
 
LVL 11

Expert Comment

by:fanopoe
ID: 22904746
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22907355
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
 
LVL 11

Expert Comment

by:fanopoe
ID: 22907575
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22907639
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22912986
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
 

Author Comment

by:trumpman
ID: 22922790
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 22923093
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22924580
Trumpman -
Provide some sample data (2-3 records) in all the tables. and your desired output.
0
 

Author Comment

by:trumpman
ID: 22926947
<%#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
 
LVL 41

Expert Comment

by:Sharath
ID: 22927113
Did you run my query. What is the output of that?
0
 

Author Comment

by:trumpman
ID: 22927186
The output in SQL is displaying correctly.  This,  however, is not the case when the results are called from my code-behind.

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22927197
>> 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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22927209
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 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