CODEWITHSUNDEEP
phpjquerymysql
chepe263
chepe263

Reputation: 2812

Need ideas: Selecting rows in table

I'm developing a site for a client. It requires a special way to create a calculation. In the table, i have to create groups of rows with the same "% End".

Table

Since there are just one row with 90% at the begining (date November 24 2011) you just ignore it. For example, i have to select the rows with dates

November 26 2011 November 28 2011 November 29 2011

And then make the calculation, later select the dates

December 04 2011 December 05 2011 December 06 2011

Rows with date December 07 2011 and December 09 are ignored too because there's no rows with the same percent before or after them.

And so on.

I'm pretty lost on how to do it. How can i create groups of dates to calculate them? Is there an easy and efficent way to do it?

My "resources" are PHP, jQuery and MySQL

Any help is appreciated, i'm stuck with this since monday.

EDIT: the information for the table is fetched by a stored procedure EDIT2: the table must start and end with an final percert of 90%

Start 90%....any values...90% end

edit3: this is my stored procedure (maybe it can help to better understanding http://pastebin.com/cZM1j72u)

to solve my problem i can use php, Javascript( jQuery ) or mysql

the idea is take the pokemon's value from date December 17th 2011 and substract from December 14th 2012.

The value is going to be divided from Dec 14th 'Galons' value. <---That's what i'm looking for.

Upvotes: 4

Views: 171

Answers (3)

tosin
tosin

Reputation: 1179

What kind of calculation do you need? I think it's ground of MySQL. For example:

SELECT percent_end, SUM(cakes) as sumcakes
FROM table
GROUP BY percent_end

This is simply SUM of cakes by each %_END. use GROUP BY.

If you don't need %_END which has only one record:

SELECT percent_end, SUM(cakes) as sum_cakes, COUNT(date) as date_amount
FROM table
GROUP BY percent_end
HAVING date_amount > 1

Upvotes: 3

SimonDowdles
SimonDowdles

Reputation: 2046

Elaborating on Tosins answer above, you could avoid calculating on single date entries by following a procedure similar to this:

SELECT percent_end, SUM(cakes) as sum_cakes, COUNT(DISTINCT(date)) as date_amount
FROM table
WHERE date_amount > 1
GROUP BY percent_end

In this case, unless there are 2 or more distinct date ranges, the record(s) will not be returned. However, if you need more complex date comparison functions, the SQL is going to start to look quite complex indeed. If you need to check - for example - if a date that falls before or after a record of the same percent is 'x' days before or 'x' days after, that will be a whole new ball game. In the case of more complex calculations, I'd personally resort to working with PHP arrays and performing various sorting and omitting functions. Many may disagree with me, but I find that sometimes you'll spend days trying to craft some cumbersome SQL query that looks like it's from mars.

* EDIT * I just realized a horrible mistake with my above example! Of course it wont work, I have used date_amount in my where clause, and date_amount was an AS field, in other words not an actual column name. Because SQL evaluates from the inside out, there WHERE clause happens BEFORE the AS clause, and so will throw an error and tell you that date_amount does not exist. Sorry about that. I am leaving it in purely in case it aids someone.

One can however alter the SQL and use the HAVING clause the get the same desired effect:

SELECT percent_end, SUM(cakes) as sum_cakes, COUNT(DISTINCT(date)) as date_amount
FROM table
GROUP BY percent_end
HAVING date_amount > 1

Upvotes: 1

Rafi
Rafi

Reputation: 545

Something like:

for(i=0; i<data.length-1; i++) {
  arr = []
  while(data[i].end_value == data[i+1].end_value) {
    arr.append(data[i].end_value)
    i++;
  }
  arr.append(data[i+1].end_value)
  if(arr.length > 1)
    // Then you've found a group and do the calculation with the values in the array
}

Adjust the syntax for whatever work you need; you probably want to do this server-side.

Upvotes: 0

Related Questions