CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Wisconsin
    Posts
    22

    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!

  2. #2
    Join Date
    Jun 2002
    Posts
    126
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    1,792
    SELECT COUNT(cnt) AS count_of_cnt, DataA, DataB
    WHERE ...
    GROUP BY ....

  4. #4
    Join Date
    Oct 2002
    Location
    Wisconsin
    Posts
    22
    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!

  5. #5
    Join Date
    Jun 2002
    Posts
    126
    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...

  6. #6
    Join Date
    Apr 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    1,792
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured