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,495
    Mentioned
    161 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,067
    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?
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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,067
    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.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


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
  •