CODEWITHSUNDEEP
sqlvb.netms-accessoledb
Joe Jones
Joe Jones

Reputation: 13

Using SQL, how do I COUNT the number of results from a query?

I'm quite new to SQL and coding generally.

I have a SQL query that works fine. All I want to do now is return the number of rows from that query result.

The current SQL query is:

SELECT 
    Progress.UserID, Questions.[Question Location],
    Questions.[Correct Answer], Questions.[False Answer 1],
    Questions.[False Answer 2], Questions.[False Answer 3]
FROM
    Questions 
INNER JOIN
    Progress ON Questions.[QuestionID] = Progress.[QuestionID]
 WHERE 
    (((Progress.UserID) = 1) AND 
     ((Progress.Status) <> "Correct")
    );

I know I need to use 

SELECT COUNT(*)

...though not quite sure how I integrate it into the query.

I then intend to use OLEDB to return the result to a VB Windows Form App.

All help is much appreciated.

Thanks! Joe

Upvotes: 0

Views: 4945

Answers (3)

peterG
peterG

Reputation: 1641

A completely different approach from the SQL based ones is to count the rows once they are back in your application - you say you are using VB so you may well be using a dataset to hold the results of your query. If so then you only need this bit of code:

dim rowcount as integer = mydataset.mytable.rows.count

Upvotes: 0

emily
emily

Reputation: 136

To count all of the records, use a simple subquery; subqueries must have aliases (here I've named your subquery 'subquery').

SELECT COUNT(*) 
FROM (
    SELECT Progress.UserID, Questions.[Question Location],Questions.[Correct Answer], Questions.[False Answer 1],
    Questions.[False Answer 2], Questions.[False Answer 3]
    FROM Questions 
    INNER JOIN Progress ON Questions.[QuestionID] = Progress.[QuestionID]
    WHERE (((Progress.UserID)=1) AND ((Progress.Status)<>"Correct"))
) AS subquery;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269543

A simple way is to use a subquery:

select count(*)
from (<your query here>) as q;

In your case, you can also change the select to be:

select count(*)

but that would not work for aggregation queries.

Upvotes: 0

Related Questions