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

Question

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

`(Progress.Status) <> "Correct"` should be `(Progress.Status) <> 'Correct'` — T.S., Jan 01 '16 at 23:31
yah, just realised, either it gets confused in vis studio. Thanks! — Joe Jones, Jan 02 '16 at 00:53

score 3 · Answer 1 · answered Jan 01 '16 at 18:23

3

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;

answered Jan 01 '16 at 18:23

emily

136
1
2

1

@LawrenceJohnson absolutely doesn't matter http://stackoverflow.com/questions/1221559/count-vs-count1 – T.S. Jan 01 '16 at 23:34
This is what I get for listening to DBAs. Thanks for linking that. – Lawrence Johnson Jan 02 '16 at 00:34

score 0 · Accepted Answer · answered Jan 01 '16 at 17:31

0

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.

answered Jan 01 '16 at 17:31

Gordon Linoff

1,242,037
58
646
786

Hi there, thanks for your response. what do you mean by "as q"? thanks – Joe Jones Jan 01 '16 at 17:35
don't worry, coz it works! Thanks so much, really is gonna help me with my computing coursework. – Joe Jones Jan 01 '16 at 17:37
SELECT Count(*) AS Expr1 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 q; – Joe Jones Jan 01 '16 at 17:37

score 0 · Answer 3 · answered Jan 01 '16 at 20:29

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

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

3 Answers3