?
Solved

Cumulative Count with MySQL and PHP

Posted on 2006-05-13
8
Medium Priority
?
666 Views
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
0
Comment
Question by:henrythacker
7 Comments
 
LVL 14

Expert Comment

by:psadac
ID: 16674438
you can do this using variables :

SELECT
  productid
  productname,
  IF(@pi = productid, @total := @total +1, @total := 1) AS CumulativeTotalSoFarPerItem,
  @pi := productid AS DoNotDisplay
FROM
  lines
ORDER BY
  productid

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

Expert Comment

by:albee01
ID: 16691604
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.

0
 
LVL 1

Author Comment

by:henrythacker
ID: 16700549
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!
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 1

Author Comment

by:henrythacker
ID: 16723767

psadac:

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.

albee01:

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

etc..

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

Author Comment

by:henrythacker
ID: 16774530
Anyone got any ideas?
0
 
LVL 1

Author Comment

by:henrythacker
ID: 16775046
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
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16796529
Closed, 500 points refunded.

GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 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