Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: Get number of occurrence

    Hi Guys help please..Say i have a table with one column and each row can have a integer value and can have a same value with other rows..so say i have 1,1,2,2 and 3 value on each row..what should be my sql query if i want to count each occurrence of the number example output:

    Col1 | NumOccurrence
    1 | 2
    2 | 2
    3 | 1


    Thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Col1
         , COUNT(*) AS NumOccurrence
      FROM daTable
    GROUP
        BY Col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Posts
    74
    Thanks! But i didn't realize what i need is more complex..Say i have three(3) tables as follows:
    Candidates(CandidateID, PositionID, DateRegistered)
    Members(MemberID, FirstName, LastName, Picture)
    Votes(VoteID, VotersID, CandidateID, PositionID, DatetimeVoted)

    How can i get the number of votes for each Candidates?...Thanks in advance!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Link candidates to votes, group by candidate, count VoteID.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Sep 2005
    Posts
    74
    Is it something like this?
    Code:
    SELECT     Candidates.CandidateID, Votes.PositionID, COUNT(Votes.CandidateID) AS [Count]
    FROM         (Candidates INNER JOIN
                          Votes ON Votes.CandidateID = Candidates.CandidateID)
    GROUP BY Candidates.CandidateID, Votes.PositionID
    if so, this is not working the way i want it to..A candidate that does not have a vote yet will not be displayed which i dont want to happen..i want to display all candidates together with their number of votes
    Last edited by daimous; 06-02-09 at 08:38.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    LEFT OUTER JOIN instead of INNER JOIN
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2005
    Posts
    74
    That solved my problem! Thanks a lot!

Posting Permissions

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