SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with counting repeating numbers

    Hello,

    First of all, I have two tables in ACCESS database.

    Results
    int ResultID auto_inc
    int ResultWeek
    int ResultYear
    DateTime ResultDate

    ResultNumbers
    int NumberID auto_inc
    int ResultID
    int ResultNumber
    bool IsBasic

    Now I want to select last 40 results from ResultNumbers and count the repeating numbers.

    I made a query lik this:
    Code:
    SELECT TOP 40 Results.ResultDate, ResultNumbers.ResultNumber, Count(ResultNumbers.ResultNumber) AS CountOfResultNumber
    FROM ResultNumbers INNER JOIN Results ON ResultNumbers.ResultID = Results.ResultID
    GROUP BY Results.ResultDate, ResultNumbers.ResultNumber
    ORDER BY Results.ResultDate DESC;
    But all I get is

    Code:
    ResultDate	ResultNumber	CountOfResultNumber
    -------------------------------------------------------------
    2.04.2008	       8	                       1
    2.04.2008	      27	                       1
    2.04.2008	      29	                       1
    2.04.2008	      30	                       1
    2.04.2008	      38	                       1
    2.04.2008	      41	                       1
    2.04.2008	      43	                       1
    2.04.2008	      44	                       1
    26.03.2008	       6	                       1
    26.03.2008	      13	                       1
    26.03.2008	      19	                       1
    26.03.2008	      33	                       1
    26.03.2008	      38	                       1
    26.03.2008	      40	                       1
    26.03.2008	      41	                       1
    26.03.2008	      44	                       1
    19.03.2008	      2	                       1
    19.03.2008	      15	                       1
    19.03.2008	      16	                       1
    19.03.2008	      25	                       1
    19.03.2008	      26	                       1
    19.03.2008	      37	                       1
    19.03.2008	      40	                       1
    19.03.2008	      48	                       1
    12.03.2008	      7	                       1
    12.03.2008	      9	                       1
    12.03.2008	      15	                       1
    12.03.2008	      22	                       1
    12.03.2008	      27	                       1
    12.03.2008	      33	                       1
    12.03.2008	      34	                       1
    12.03.2008	      40	                       1
    5.03.2008	      4	                       1
    5.03.2008	      5	                       1
    5.03.2008	      6	                       1
    5.03.2008	      14	                       1
    5.03.2008	      18	                       1
    5.03.2008	      29	                       1
    5.03.2008	      42	                       1
    5.03.2008	      46	                       1
    Number 6 is here 2 times, but why this query returns all counts in different rows?

    I want that result will be like:

    Code:
    ResultNumber	CountOfResultNumber
    -----------------------------------------------------------
                    8                             1
                   27                            2
                   29                             2
                   30                             1
                   38                             2
                   41                             2
                   43                             1
                   44                             2
    	   6	                     2
                   13                             1
                   19                             1
                   33                             2

    I know that there's a problem with date, but I'm not quite home with databases. Please can someone give me hand?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by evilone View Post
    Now I want to select last 40 results from ResultNumbers and count the repeating numbers.
    last 40 results:

    Code:
    SELECT TOP 40 
           Results.ResultDate
         , ResultNumbers.ResultNumber
      FROM Results  
    INNER 
      JOIN ResultNumbers
        ON ResultNumbers.ResultID = Results.ResultID
    ORDER 
        BY Results.ResultDate DESC
    count repeating numbers in last 40 results:
    Code:
    SELECT ResultNumber
         , COUNT(ResultNumber) AS CountOfResultNumber
      FROM ( SELECT TOP 40 
                    Results.ResultDate
                  , ResultNumbers.ResultNumber
               FROM Results  
             INNER 
               JOIN ResultNumbers
                 ON ResultNumbers.ResultID = Results.ResultID
             ORDER 
                 BY Results.ResultDate DESC ) AS last40
    GROUP 
        BY ResultNumber
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this works great, I learn so much from you r937, 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
  •