Count The Number Of Records Returned From A SELECT Query

[SeanK]

I need to know how many records are returned each time a particular SELECT query is run. I know that is I use a Static cursor, I can use the ADO Recordcount, but I'd rather include something in my SQL statement.

There are no numeric fields in my SELECT so SUM doesn't work. When I run the query in Query Analyser (SELECT COUNT(*) AS tot, field1, field2, field3, blah, blah blah) the tot column, all the way down the rows returned is a only a 1 and I need something that will return the total number of records in my recordset.

[si_the_geek]

For the Count (or other aggregate functions like Sum) to work you cannot have any fields in your Select clause - so you would need to run a separate query (with the same From & Where clause etc) to get the Count.

[szlamany]

As Si said:

Select ColA, ColB, ColC
    ,(Select Count(*) From SomeTable ST2 Where ST2.ColXYZ='Whatever')
    From SomeTable ST1
    Where ST1.ColXYZ='Whatever'

Really should be done in the same query - in a multi user system anything else would be open to inconsistent values...

[SeanK]

When I run the standard query in Query Analyser it returns 133 records.

When I run the same query with nothing in the SELECT clause except SELECT COUNT(*) As tot
with same FROM
same WHERE
same JOINS
same ANDs
same GROUP BY
same ORDER BY

it also returns 133 records with 1 in all 133 rows.

[szlamany]

Show your query...

The COUNT(*) needs to be in a sub-query - as I showed.

Careful with the alias names - as you end up with ambiguities that will not resolve properly.

[si_the_geek]

Really should be done in the same query - in a multi user system anything else would be open to inconsistent values...

I hadn't thought of that, but I fully agree.

same GROUP BY
same ORDER BY

Order By is irrelevant, and Group By will just cause problems - presumably the cause of the issue here.

[SeanK]

Show your query

gstrReportSQL = "SELECT assign.prov_cd, rev.bip_fy_end_dt, bip_data_attr_cd, bip_rev_eff_dt, new_bip_amt, lump_sum_amt, "
gstrReportSQL = gstrReportSQL & "bip_rev_dt, no_bip_dt, no_bip_change_dt, prov.prov_nm, rev.comments, specialist_assign_id,  "
gstrReportSQL = gstrReportSQL & "assign.ip_est_gain_loss_amt, assign.op_est_gain_loss_amt "
gstrReportSQL = gstrReportSQL & "FROM customer_review rev "
gstrReportSQL = gstrReportSQL & "INNER JOIN customer_assign assign ON rev.prov_cd = assign.prov_cd "
gstrReportSQL = gstrReportSQL & "AND rev.prov_rcd_eff_dt = assign.prov_rcd_eff_dt "
gstrReportSQL = gstrReportSQL & "AND rev.bip_fy_end_dt = assign.bip_fy_end_dt "
gstrReportSQL = gstrReportSQL & "INNER JOIN providers prov ON assign.prov_cd = prov.prov_cd "
gstrReportSQL = gstrReportSQL & "AND assign.prov_rcd_eff_dt = prov.prov_rcd_eff_dt "
gstrReportSQL = gstrReportSQL & "WHERE bip_rev_dt IS NOT NULL "
gstrReportSQL = gstrReportSQL & "AND (no_bip_dt IS NULL OR no_bip_change_dt IS NULL "
gstrReportSQL = gstrReportSQL & "OR bip_rev_eff_dt IS NOT NULL) "
gstrReportSQL = gstrReportSQL & "AND (DATEPART(YYYY, rev.bip_fy_end_dt)) = '" & frmBIPInProcess.cboYear.Text & "' "
gstrReportSQL = gstrReportSQL & "GROUP BY assign.specialist_assign_id, assign.prov_cd, rev.bip_fy_end_dt, bip_data_attr_cd, "
gstrReportSQL = gstrReportSQL & "bip_rev_eff_dt , new_bip_amt, lump_sum_amt, bip_rev_dt, "
gstrReportSQL = gstrReportSQL & "no_bip_dt , no_bip_change_dt, prov.prov_nm, rev.Comments, "
gstrReportSQL = gstrReportSQL & "assign.ip_est_gain_loss_amt, assign.op_est_gain_loss_amt "

I would love to make this a parametized query but I have no idea how to. For now I'll settle for getting an accurate recordcount.

[si_the_geek]

With a Group By, any aggregate functions (such as Sum/Count) only operate on the grouped rows, not on the entire set of data.

In order to get the overall rowcount for a Group'ed set of records, I think you will need to use a separate query for the count, with a sub-query to get the grouping too, eg:

strCountSQL = "SELECT Count(A.*) FROM (" & gstrReportSQL & ") as A"

[szlamany]

You will need to put that query into a VIEW (you should anyway!)

And of course that will only work if every field in the WHERE clause is in the GROUP BY also.

Note this won't work:

select gender
   ,(select count(*) from student_t group by gender)
   from student_T group by gender

Gets this error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


But this will work:

create view gender_v as select gender from student_t group by gender
go
select *,(select count(*) from gender_v) from gender_v

returns...

gender             
------ ----------- 
F      2
M      2

(2 row(s) affected)

Also - if this is MS SQL server you can do this in a STORED PROCEDURE.

Put the "resultset" into a table variable or temp table - and then SELECT out of that for return to the VB client side.

[SeanK]

You will need to put that query into a VIEW (you should anyway!)

And of course that will only work if every field in the WHERE clause is in the GROUP BY also.

Note this won't work:

select gender
   ,(select count(*) from student_t group by gender)
   from student_T group by gender

Gets this error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


But this will work:

create view gender_v as select gender from student_t group by gender
go
select *,(select count(*) from gender_v) from gender_v

returns...

gender             
------ ----------- 
F      2
M      2

(2 row(s) affected)

Also - if this is MS SQL server you can do this in a STORED PROCEDURE.

Put the "resultset" into a table variable or temp table - and then SELECT out of that for return to the VB client side.

The one and only purpose for this query is to dump the resulting recordset to an excel spreadsheet to be printed as a report. Also, that is not the entire query. It wouldn't let me post the entire query as it was too long. The parts I didn't post deal with listbox selections, option buttons choices and check box choices all of which add AND clauses and a couple of IN clauses to the whole thing.

I'm thinking I should probably just use a static cursor and use the recordcount property of the ado recordset. I need to do grand totals on two of the columns for the report. I know what columns will be used, but what I don't know is where to place the total amount because I will never know how many records will be in the recordset. That will depend entirely on what is selected, or not selected, from the VB form used as this reports criteria page.

[techgnome]

Why not select the data, dump it into Excel, then use Excel to do the counting and calculations? Once you've got it in Excel, it's not that hard to add formulas to it...

-tg

[szlamany]

Can you UNION ALL a second recordset onto this recordset that has your totals? And somehow get that to be the bottom row?

[SeanK]

Why not select the data, dump it into Excel, then use Excel to do the counting and calculations? Once you've got it in Excel, it's not that hard to add formulas to it...

-tg

If I don't know how many records will be returned each time the query is run, I will never know how far down the page the data will stretch, so how will I know what row to put the forumla in?

[SeanK]

Can you UNION ALL a second recordset onto this recordset that has your totals? And somehow get that to be the bottom row?

An interesting thought, but I have no idea how I would go about it. I mean, I'm familiar with UNION and UNION ALL, but I'm not grasping how I would use these in a second recordset to get my column totals.

[szlamany]

Select 1,Gender, Count(*) From Student_T Group by Gender

Union All

Select 2, 'Total', Count(*) From Student_T

Order by 1,2

[edit] hey - I passed 7000 posts!

[SeanK]

[edit] hey - I passed 7000 posts!

Congratulations!!!

Select 1,Gender, Count(*) From Student_T Group by Gender

Union All

Select 2, 'Total', Count(*) From Student_T

Order by 1,2

This has possibilities. Let me give this a whirl around the block and see what happens.

Edit: On second thought, don't I need the same number of somethings (I don't remember what now) in both SELECTS? If my first SELECT has a ton of fields and a GROUP BY and my second SELECT only has a COUNT and no GROUP BY isn't SQL Server going to throw up?

[techgnome]

If I don't know how many records will be returned each time the query is run, I will never know how far down the page the data will stretch, so how will I know what row to put the forumla in?

UsedRange returns a cell collection that have data in them.
UsedRange.Rows will give you the rows that are in the used range.
UsedRange.Rows.Count will tell you how many rows are in the used range.

-tg

[techgnome]

When I run the query in Query Analyser

What the he11? You're using SQL Server? What the fork? Stored procedure man... use a fraking stored procedure.... let it return two results, one containing the data, and one containing the results. Use a server-side cursor to get the first recordset, once you reach the end of it, use the .NextRedordset to get to the next set of data, your totals, and stuff that into Excel....

-tg

[szlamany]

SeanK - the only requirement in the UNION ALL is both having the same number of columns (and similar datatypes in those columns).

TG - if you are going to go down the SPROC angle, I would create a TEMP TABLE before using a CURSOR...

Just my preference...

But you are right - returning two recordsets from a SPROC is a proper way to do this.

[SeanK]

What the he11? You're using SQL Server? What the fork? Stored procedure man... use a fraking stored procedure.... let it return two results, one containing the data, and one containing the results. Use a server-side cursor to get the first recordset, once you reach the end of it, use the .NextRedordset to get to the next set of data, your totals, and stuff that into Excel....

-tg

I would be more than happy to use a stored procedure if you could answer a question that I've already asked, and no one did answer.

How do I:

Pass the selections from a multiselect listbox on a VB Form to a stored procedure and insert into an IN clause in said stored procedure?

Execute or not execute an AND clause in said stored procedure depending on whether a particular checkbox on a VB form is checked or not?

Have two AND clauses in said stored procedure but only one gets executed depending on what option button on VB Form is checked?

Pass a ListView text and subitems text to an AND clause in said stored procedure?

[SeanK]

UsedRange returns a cell collection that have data in them.
UsedRange.Rows will give you the rows that are in the used range.
UsedRange.Rows.Count will tell you how many rows are in the used range.

-tg

Never used those before. Looks like it is Google time.

[szlamany]

Ok - I've answered that multi-select IN clause issue several times...

I've added it to my signature - "Passing Multi-Item Parameters to Stored Procedures"

I pass in values from checkboxes all the time - as INT for example...

Then: WHERE blah and blah and blaH and (@ChkBox=1 or blah)

Same with the OPTION button:

Where (blah and @OptButton=0) or (blah and @OptButton=1)

Or even better:

If @OptButton=0
Begin
.
.
do a query...
.
.
End
Else
Begin
.
.
do a different query...
.
.
End

[SeanK]

I've read the thread in the link in your signature and it confuses the hell out of me.

Maybe one day when I'm not under a deadline gun I'll play around with it.

[si_the_geek]

I'm a bit late coming back to the party, but my suggestion is exactly the same as TG posted in #17.

Rather than create lots of extra work for yourself, simply use one easy line of code to find out how many rows have been put into Excel. If you need more clarification about using UsedRange, see the post about it in my Excel tutorial (link in my signature).

[SeanK]

Is UsedRange a property of the Workbook object or the Worksheet object?

[si_the_geek]

As with all ranges, it applies to the WorkSheet.

[SeanK]

Ok. I have the following declarations in a .bas module that I will be using for my reports

Public objExcel As Excel.Application
Public bkWorkBook As Workbook
Public shWorkSheet As Worksheet
Public oWin As Excel.Window
Public rngRowStart As Excel.Range

Now, in my report sub, I'm doing (as a test)

Msgbox shWorkSheet.UsedRange.Value

And I'm receiving a Type Mismatch error.

[si_the_geek]

From post #8 of my tutorial:

LastRow = oXLSheet.UsedRange.Rows.Count

[SeanK]

Yep...that works!

Thanks.

[techgnome]

TG - if you are going to go down the SPROC angle, I would create a TEMP TABLE before using a CURSOR...

A cursor? nononono.... I'd never use a cursor for this.... didn't realize that's how it came across.... OH.... wait.... you're talkoing about this: "Use a server-side cursor to get the first recordset," I didn't mean a cursor in the SPRoc on the server.... I meant, when creating the connection, using .CursorLocation = adServerSide and not adClientSide..... I was thinking that it was necessary to use the Multi-Recordset ... but now thaT I think about it..... I think I may have seen evidence to the contrary, that it doesn't matter.

-tg

[szlamany]

tg - makes sense

I wish SeanK would find the time to leave that "write a query" in VB world and enter the land of SPROCS...

I could never imagine writing a string-concatenated mess of textbox's and query literals to get a recordset anymore.

One of my latest requests from a customer was to allow a query to be run in our app so the user could save it to a text file for import into EXCEL. I'm thinking this is VB coding - this is where to save a file - this is all kinds of non-standard, non-windows training...

After about 5 minutes I realized we needed to build a VIEW - make the VIEW realize the USERNAME of the person connected - so that only kids from their building were visible - even add other layers of SQL-side role security...

Then build an ODC file so that they could use the IMPORT option from EXCEL and make there own spreadsheets - no need to visit my app for that.

Now the spreadsheet is live - they can add columns - and still refresh with new students whenever they want...

When we do financial displays in flexgrids - we do it all in SPROCS - and we do all the totalling with UNION's and fancy order by's. We never write any VB code anymore - it's all about putting queries into SPROCS...

I think I might need a few days off

[techgnome]

Tell you what.... take the next two days off.....

I had to convince a client recently to use sprocs instead of inline SQL ... once I pointed out the cost of maintaining the inline queries.... he cried uncle.

-tg