SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Getting the total quantity of each item in a table

    Good day,

    I am working in an online website for selling books.
    There is a table where the sold books are stored, like this:

    Purchases
    ======
    id | int - autoincrement
    Date
    CustomerID (foreing key to table Customers)
    BookID (foreign key to table Books)

    How can I calculate the total quantity of sold books of each one?
    For instance, having 'n' books, how many BookID = 1 to n have been sold (how many of each one)?

    Thanks a lot!!!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT
        BookID
      , COUNT(*) AS Total
    FROM Purchases
    GROUP BY BookID

  3. #3
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi guido2004,

    One last question please. Where are the result stored and how can I read them?
    I understand I have to run this query and assign a variable to the result.
    Is this result an array, having the totals of each BookID per rows?

    Thanks a lot for your answer!!

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    What language are you using? As that question may be better suited in the appropriate forum. If you are using PHP, are you utilizing PDO, MySQLi, MySQL?

  5. #5
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I am using PHP and MySQL.

    Thanks a lot!

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,158
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, well then you can utilize several different options.
    mysql_ fetch_ array, mysql_ fetch_ assoc, and mysql_fetch_row

    All of the above options have examples of how to use them.


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
  •