CODEWITHSUNDEEP
asp.netsql
ONYX
ONYX

Reputation: 5859

SQL: Need to get a count of records

This should be simple Im just not getting the desired result, i've tries this a couple of ways and still not getting anywhere I need to get the total cound of records of the subquery I know this is totally wrong but when but when I put the count on the subquery it still gives me 1 here's my code:

SELECT COUNT(*) [COUNT] 
    (
        SELECT WP_BlogEntries.BlogEntryID, sys_Objects_Tags.TagID
        FROM WP_BlogEntries INNER JOIN sys_Objects_Tags ON
        WP_BlogEntries.BlogEntryID = sys_Objects_Tags.SystemObjectRecordID
        WHERE WP_BlogEntries.ReleaseDate < GETDATE()
        AND WP_BlogEntries.ExpireDate > GETDATE()
        AND WP_BlogEntries.Approved = 1
        AND WP_BlogEntries.Listed = 1
        AND WP_BlogEntries.BlogID = @BlogID
        AND TagID = @TagID
        GROUP BY WP_BlogEntries.BlogID, BlogEntryID, sys_Objects_Tags.TagID
    )

Upvotes: 1

Views: 220

Answers (3)

Zo Has
Zo Has

Reputation: 13028

select count(*)as COUNT from (select * from tableName where condition='this' group by ID)as s

Upvotes: 0

Thomas
Thomas

Reputation: 64645

SELECT COUNT(*) As [COUNT] 
From (
    SELECT WP_BlogEntries.BlogEntryID, sys_Objects_Tags.TagID
    FROM WP_BlogEntries 
        INNER JOIN sys_Objects_Tags 
            ON WP_BlogEntries.BlogEntryID = sys_Objects_Tags.SystemObjectRecordID
    WHERE WP_BlogEntries.ReleaseDate < GETDATE()
        AND WP_BlogEntries.ExpireDate > GETDATE()
        AND WP_BlogEntries.Approved = 1
        AND WP_BlogEntries.Listed = 1
        AND WP_BlogEntries.BlogID = @BlogID
        AND TagID = @TagID
    GROUP BY WP_BlogEntries.BlogID, BlogEntryID, sys_Objects_Tags.TagID
    ) As Z

You simply need to alias the subquery and put it into the FROM clause making it into a derived table. Although, if all you want is the count, you don't need anything in the Select clause (i.e., you could use Select 1 in the subquery's Select clause and it'd still work.

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89661

How many rows does this return with your normal parameters in @BlogID, @TagID?:

SELECT WP_BlogEntries.BlogEntryID, sys_Objects_Tags.TagID
        FROM WP_BlogEntries INNER JOIN sys_Objects_Tags ON
        WP_BlogEntries.BlogEntryID = sys_Objects_Tags.SystemObjectRecordID
        WHERE WP_BlogEntries.ReleaseDate < GETDATE()
        AND WP_BlogEntries.ExpireDate > GETDATE()
        AND WP_BlogEntries.Approved = 1
        AND WP_BlogEntries.Listed = 1
        AND WP_BlogEntries.BlogID = @BlogID
        AND TagID = @TagID
        GROUP BY WP_BlogEntries.BlogID, BlogEntryID, sys_Objects_Tags.TagID

And, as others have pointed out, your inner query needs a name:

SELECT COUNT(*) FROM (
SELECT WP_BlogEntries.BlogEntryID, sys_Objects_Tags.TagID
        FROM WP_BlogEntries INNER JOIN sys_Objects_Tags ON
        WP_BlogEntries.BlogEntryID = sys_Objects_Tags.SystemObjectRecordID
        WHERE WP_BlogEntries.ReleaseDate < GETDATE()
        AND WP_BlogEntries.ExpireDate > GETDATE()
        AND WP_BlogEntries.Approved = 1
        AND WP_BlogEntries.Listed = 1
        AND WP_BlogEntries.BlogID = @BlogID
        AND TagID = @TagID
        GROUP BY WP_BlogEntries.BlogID, BlogEntryID, sys_Objects_Tags.TagID
) PLEASE

Upvotes: 0

Related Questions