Query record count using only SQL

I am running a SQL command through VB and need to find a count of records returned in the query. I know this sounds simple, however the problem is that it ABSOLUTELY MUST be done in SQL. No recordcounts or while loops. Unfortunately, I can't get the count command to work efficiently here because the count command counts records in a group, not in a table, unless the table is one group. Let me explain what I need further.

Say I have a table with three columns Cnt, DataA, and DataB. I need a list containing DataA and DataB where Cnt contains the count of all records in the table. This means Cnt has the same value in every record.

Cnt DataA DataB
3 1 x
3 4 y
3 2 z

If the data is in the table Tbl, then the closest I have been able to come is

SELECT
(SELECT COUNT(*) FROM Tbl WHERE... GROUP BY...)
AS Cnt,
DataA,DataB FROM Tbl WHERE... GROUP BY...

This method does work, however I am also working under space restraints, so it would be nice if I don't have to repeat the select statement twice in the same SQL call.

Does anyone have any ideas?

Thanks in advance!

I guess your select query is the best you can do. What kind of space limits have you got, may-be there is some other workaround to your space problem.

SELECT COUNT(cnt) AS count_of_cnt, DataA, DataB
WHERE ...
GROUP BY ....

Twodogs: I have tried your solution, but I'm sorry to say it did not give me what I need. The count_of_cnt column returns a count of records in a each group instead of a count of records in the query table.

lehmaeit: My space restriction limits me to groups of 500 character strings. I can break the SQL command up into 500 character groupings, but each additional grouping causes a reduction in speed efficiency. With my project, speed has highest priority. I can't explain why here, unfortunatly. The space limitation is from my own design, but it was neccesary for increasing speed.

It would probably be helpful to mention that I'm using Sybase Adaptive Server Anywhere for the database.

Also, I did learn from another source that I can use COUNT(DISTINCT cnt) to eliminate the need for the GROUP BY clause in the sub-query. It shortens my statements some.

Thanks for your help!

I think COUNT(DISTINCT x) is slower because SQL engine has to sort values (but I may be wrong)

There's still one thing you can do. Create a view like this

CREATE VIEW CountView AS
(SELECT COUNT(*) Cnt FROM Tbl WHERE... GROUP BY...)

And then your query will look like

SELECT
CountView.Cnt,
DataA,DataB FROM Tbl INNER JOIN CountView WHERE... GROUP BY...

I'm also using Sybase here - what I'd do is put it all in a stored proc, and just call the stored proc. That way, you can get the count done and put it into a variable, and use the variable in your resultset. If you replace the ...'s with the actual stuff you need, and run this into your database, you can call it and it should give you the resultset you want.

Code:

---

IF OBJECT_ID(USER_NAME()+'.p_get_count') IS NOT NULL
BEGIN
    DROP PROCEDURE p_get_count
    IF OBJECT_ID(USER_NAME()+'.p_get_count') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE p_get_count >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE p_get_count >>>'
END
GO
CREATE PROCEDURE p_get_count
AS
DECLARE @count INT
SELECT @count = SELECT COUNT(cnt) AS count_of_cnt
                FROM tbl
       
SELECT @count 'MyCount', DataA, DataB
FROM tbl
WHERE ...
GROUP BY ...

go
GRANT EXECUTE ON p_get_count TO ...
go

IF OBJECT_ID(USER_NAME()+'.p_get_count') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE p_get_count >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE p_get_count >>>'
go

---