Lets say I needed to see if the amount of sales exceeded expenses for a product by week, and then count the number of weeks out of the year that a product generated a sale.
I have one table with over 300,000 records which contains the data for the year. I select a date range to look at and in that date range is 100,000 records. So I copy those records to another table.
This part works and the query is something like this:
"INSERT INTO new_table (data from first_table) WHERE DATE BETWEEN (date range specified)"
Then I want to get the sum of the data and count the number of weeks the product had a sale.
Right now I have something like this:
I run a query to get the sum of the data and name of the product (this works), then I use something like this "mysql_num_rows(mysql_query(select product from table where product=product and trans > 0))" to get the count (not exact syntax).
I also tried "SELECT COUNT(Product) as count FROM table WHERE Product='$product' AND Trans > 0"
Both approaches take so long that the page times out. And I'm using "or die( mysql_error())" with no errors. The end result should return data for about 20,000 products.
Any suggestions? Thanks in advance.
