itzumar
Posts: 88
|
Posted: 12/28/2013, 12:47 AM |
|
hello!
how to Select Mysql commutative sum of each row by programmatic logic in code codecharge studio? I am using php and mysql in codecharge charge????????
_________________
Umar |
|
|
osulywan
Posts: 16
|
Posted: 12/30/2013, 7:31 AM |
|
I would let MySQL do it for me. If you want company, date and amount (for instance):
Select company,date, sum(amount) from some_table
Where 1=1
group by company,date
Let code charge display results.
Code Charge Report Builder has some calc fields you could exploit for this, too.
|
|
|
osulywan
Posts: 16
|
Posted: 12/30/2013, 7:32 AM |
|
I would let MySQL do it for me. If you want company, date and amount (for instance):
Select company,date, sum(amount) from some_table
Where 1=1
group by company,date
Let code charge display results.
Code Charge Report Builder has some calc fields you could exploit for this, too.
|
|
|
itzumar
Posts: 88
|
Posted: 12/30/2013, 7:38 AM |
|
dear sir!
when i do it via mysql than performance effect and querry take lot of time to show result as i have more than 1 million records
_________________
Umar |
|
|
MichaelMcDonald
Posts: 640
|
Posted: 12/30/2013, 1:35 PM |
|
Maybe something like:
$SQL = "SELECT value FROM .....
while ($db->next_record)(){
$result = $db->f("value");
}
$totalvalue = $totalvalue + $result;
The key to making this work could be ensuring the sum occurs outside the "while"
_________________
Central Coast, NSW, Australia.
|
|
|
eratech
Posts: 513
|
Posted: 12/30/2013, 8:25 PM |
|
(seems I can use the same answer for 2 questions today.... lucky me)
CCS uses a similar method when it's working out the running totals for Reports - I think it's in the Report - Before Show Row.
Either check the Help File (I agree - it really is quite good) and then make a sample Report to see the generated code.
However, you might need to look at what you are doing in the query - MySQL (or any DB ) will be faster at doing selections and totals than moving the entire set back to PHP and looping through it.
Also - do you really need to retrieve all million records? It's rare that someone needs all the records all of the time - you might have a 'Last 30 days' report that can loop quickly through the last month of records (for example) but if they want the full report then warn that it will take longer. This way the full, slow report is not the default so they get what they want quickly, and can do the rare ones later.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
laneoc
Posts: 154
|
Posted: 12/31/2013, 8:39 PM |
|
Use compound SQL statement.
_________________
Lane |
|
|
|