SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Count

  1. #1
    SitePoint Addict
    Join Date
    Oct 2002
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count

    Hi,

    I have not strayed away from basic SQL queries (although this is not really a compelx query).

    Could anyone give me advice on doing this query

    I have a table which lists stocks and the number of times they have been brought each day. There is a only a record if that stock has been brought.
    I want a query taht will bring back the sum of the totla number per month for each stock.

    This is how the data could look in the table

    HTML Code:
    <table><tr><td>Stock</td><td>count</td><td>day</td><td>month</td></tr>
    <tr><TD>500</td><td>4</td><td>10</td><td>3</td></tr>
    <tr><TD>409</td><td>6</td><td>10</td><td>3</td></tr>
    <tr><TD>407</td><td>3</td><td>10</td><td>3</td></tr>
    <tr><TD>500</td><td>3</td><td>11</td><td>3</td></tr>
    <tr><TD>454</td><td>6</td><td>11</td><td>3</td></tr>
    <tr><TD>500</td><td>1</td><td>12</td><td>3</td></tr>
    <tr><TD>498</td><td>4</td><td>12</td><td>3</td></tr>
    <tr><TD>487</td><td>5</td><td>12</td><td>3</td></tr></table>
     
    So for example for stock 500 it would add all the counts together (3+3+3).
    Then I need the query to sort to totals so that they are in DESC order.

    ANy ideas?
    Eternity

  2. #2
    SitePoint Addict liquidautumn's Avatar
    Join Date
    Nov 2002
    Location
    Kharkov, Ukraine
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT SUM(count) AS total FROM yourtable WHERE month=3 GROUP BY stock ORDER BY total DESC

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    SELECT stock, SUM(count) AS total FROM yourtable WHERE month=3 GROUP BY stock ORDER BY total DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict liquidautumn's Avatar
    Join Date
    Nov 2002
    Location
    Kharkov, Ukraine
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, you're scaring me

  5. #5
    SitePoint Addict
    Join Date
    Oct 2002
    Posts
    229
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cheers guys
    Eternity


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
  •