Cumulative Count with MySQL and PHP

Posted on 2006-05-13
Last Modified: 2008-02-01
Hi there, this is quite complicated to explain, although I think the idea is very straightforward, but unfortunately I have no idea how to solve the problem without reverting to subqueries which will really slow everything down. Basically, I have a "standard" product database (although not completely normalised, in that the Supplier is stored with each product), however, I can not work out how to get MySQL to give me a cumulative count. To clear up the confusion, I will show briefly how it works:

Table:                            Table:                              Table:
Products                         Orders                              Lines
ProductId (primary key)   OrderId (primary key)         LineId (primary key)
Other Fields...                 Other Fields...                   OrderId (foreign key)
                                                                             ProductId (foreign key)
                                                                             Other Fields...

So when we get orders into the system, i produce a report which shows which orders are on the system. Naturally, many people can place orders for the same item on the system. So in the report I need to list the name of the item they are ordering. However, as we may have 70 of one type of item, i would like a listing as follows:

CumulativeTotalSoFarPerItem    |  ProductName
1                                             |  Product 1
2                                             |  Product 1
3                                             |  Product 1
4                                             |  Product 1           < For each item, the report counts up
1                                             |  Product 2           < how many there are of each item
2                                             |  Product 2           < as it goes up
1                                             |  Product 3
2                                             |  Product 3
3                                             |  Product 3

Alternatively, if this is easier, I am quite happy to have just the total per item printed next to the last item.

The thing is, I want to try and avoid a subquery if at all possible to look up for each one as this will not be efficient and also I don't want to use PHP to keep a running count for each product as I will only want this value displayed if say there are 10 or more of 1 type of item. So if possible I would like a variable column created in MySQL when the report is run.

Hopefully I made some sense with that! If not, please ask for clarification
Question by:henrythacker
    LVL 14

    Expert Comment

    you can do this using variables :

      IF(@pi = productid, @total := @total +1, @total := 1) AS CumulativeTotalSoFarPerItem,
      @pi := productid AS DoNotDisplay

    but i am not sure to understand what you want to do with this
    LVL 1

    Expert Comment

    Why not do something like this:

    select count(*) as c,product_name from table group by product_name having c >= 10;

    That will count all the occurrences of each product name and put it in a column labeled c where c is 10 or higher.

    LVL 1

    Author Comment

    this looks great, unfortunately am not at work until next saturday, so will try both methods out and see what seems to work better.. many thanks!
    LVL 1

    Author Comment



    This is the end version of the SQL i have ended up with:

    SELECT ProductName, Products.ProductId,
    IF (@pi = Products.ProductId, @total := @total +1, @total :=1
    ) AS CumulativeTotalSoFarPerItem, @pi := Products.ProductId AS DoNotDisplay
    FROM Line, Products, wants
    WHERE Line.ProductId = Products.ProductId
    AND Line.WantId = wants.WantId
    ORDER BY ProductId

    It seemed promising, but the CumulativeTotalSoFarPerItem column doesn't increment, the values all stay at 1.


    Naturally, this works, but I need to list each individual line like showed in my example, so if there are 50 of one item, they must all be listed as each line will have a different associated customer.


    To anyone:

    Naturally, I have placed the question now, so I may not be allowed to add this piece of criteria, but if anyone has any ideas.. the cumulative total must also take into account the QTY field in line.. so if someone orders 10 of one item, this must be added on like so:

    ProductName Qty CumulativeTotal   CustomerName
    Product1        1         1                    Mr XYZ
    Product1        2         3                    Mrs ABC
    Product1        1         4                    Mr Test
    Product2        5         5                    Miss QAS
    Product2        1         6                    Mr Jones


    If anyone can assist further, it will be greatly appreciated
    LVL 1

    Author Comment

    Anyone got any ideas?
    LVL 1

    Author Comment

    I have managed to solve the problem myself using PHP and it actually works quicker than I expected it to work. Thanks for your help anyway

    Accepted Solution

    Closed, 500 points refunded.

    The Experts Exchange
    Community Support Moderator of all Ages

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now