SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2006
    Location
    Cardiff, Wales. UK
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Count where entries are < 4

    I want to count how many users have 4 or less entries in the table, so, so far i have
    Code SQL:
    SELECT COUNT (*) AS fourLOCS FROM 
    (SELECT DISTINCT locsLog.locStudentID FROM locsLog)
    which counts all the entries in the locsLog table and groups them by studentID so tells me how many users have made entries.
    How do I limit this so it only gives me a total number of users who have made 4 or less entries. I think I'd need WHERE after the nested SELECT statement, but I don't know what I'd put in it. I tried
    Code SQL:
    SELECT COUNT (*) AS fourLOCS FROM 
    (SELECT DISTINCT locsLog.locStudentID FROM locsLog) 
    WHERE fourLOCS <= 4
    but had an error message (I'm doing it with an Access database with ASP and the message was 'Too few parameters. Expected 1').
    Does anyone have any suggestions please?

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select locStudentId, count(*)
       from locsLog
    group by locStudentId
    having count(*) <= 4

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2006
    Location
    Cardiff, Wales. UK
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's great, thank you!


Bookmarks

Posting Permissions

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