How can I nest a count within a sql query

I have the following two queries.

The first lists users and last login dates like this:

iUserNum  chDefaultLogin  dateLastLogin
--------  --------------  -------------
17        fred.bloggs     01/06/2011
23        john.doe        13/05/2011

using the following query

SELECT SDToken.iUserNum, chDefaultLogin, dateLastLogin
FROM SDUserScope JOIN SDToken ON SDUserScope.iUserNum = SDToken.iUserNum

The second one gives a count of the number of times a user has logged in (given the iUserNum user number) during the last 7 days using this query (in the example below I have given the iUse开发者_如何学编程rNum of 17):

SELECT COUNT(*) FROM SDToken 
   JOIN SDLogEntry ON SDLogEntry.chTokenSerialNum = SDToken.chSerialNum 
   JOIN SDUserScope ON SDToken.iUserNum = SDUserScope.iUserNum 
   JOIN SDLogMessage ON SDLogEntry.iMessageNum = SDLogMessage.iMessageNum 
   WHERE SDToken.iUserNum = 17 AND SDUserScope.iSiteNum = iMySite
     AND SDLogEntry.dtGMTDate > GMTDateNow - 7

What I want to do is join the two together, so I get the count for every user in the table, like this:

iUserNum  chDefaultLogin  dateLastLogin  loginCount
--------  --------------  -------------  ----------
17        fred.bloggs     01/06/2011     12
23        john.doe        13/05/2011     4

Have you try to unify the queries and just add a Group by ?

SELECT  SDToken.iUserNum, 
        chDefaultLogin, 
        dateLastLogin,
        COUNT(*) AS loginCount
FROM SDUserScope 
    INNER JOIN SDToken 
        ON SDUserScope.iUserNum = SDToken.iUserNum
            INNER JOIN SDLogEntry 
                ON SDLogEntry.chTokenSerialNum = SDToken.chSerialNum 
                    INNER JOIN SDLogMessage 
                        ON SDLogEntry.iMessageNum = SDLogMessage.iMessageNum 
WHERE   SDToken.iUserNum = 17 
AND     SDUserScope.iSiteNum = iMySite 
AND     SDLogEntry.dtGMTDate > GMTDateNow - 7
GROUP BY    SDToken.iUserNum, 
            chDefaultLogin, 
            dateLastLogin

2 subqueries joining on the username as common key should do the job in MS Sql server /SYBASE it will lokk like below but any other RDBMS you can apply same logic using 2 subqueries...syntax might differ a little buit

Select A.SDToken.iUserNem, A.chDefaultLogin, A.dateLastLoginm,B.logincount
    FROM 
    (
        (SELECT SDToken.iUserNem, chDefaultLogin, dateLastLogin FROM SDUserScope JOIN SDToken ON SDUserScope.iUserNum = SDToken.iUserNum )A
        INNER JOIN 
        (SELECT COUNT(*)logincount,SDToken.iUserNem FROM SDToken    
         JOIN SDLogEntry ON SDLogEntry.chTokenSerialNum = SDToken.chSerialNum    
         JOIN SDUserScope ON SDToken.iUserNum = SDUserScope.iUserNum    
         JOIN SDLogMessage ON SDLogEntry.iMessageNum = SDLogMessage.iMessageNum    
         WHERE SDToken.iUserNum = 17 AND SDUserScope.iSiteNum = iMySite AND SDLogEntry.dtGMTDate > GMTDateNow - 7 
         GROUP BY SDToken.iUserNem)B
         ON A.iUserNem = B.iUserNem
    )